I am trying to create an index on the following view:
我正在尝试在以下视图上创建索引：
SELECT 'Candidate' AS Source, CandidateID AS SourceId, LastName + ', ' + FirstName AS SourceName
FROM dbo.Candidates
UNION
SELECT 'Resource' AS Source, ResourceID AS SourceId, LastName + ', ' + FirstName AS SourceName
FROM dbo.Resources
UNION
SELECT 'Deal' AS Source, DealID AS SourceId, CONVERT(varchar, Number) + '-' + CONVERT(varchar, RevisionNumber) AS SourceName
FROM dbo.Deals
UNION
SELECT 'Job Order' AS Source, JobOrderID AS SourceId, CustomerNumber AS SourceName
FROM dbo.JobOrders
I am getting the following error:
我收到以下错误：
Msg 1939, Level 16, State 1, Line 2
Cannot create index on view '_Source' because the view is not schema bound.
I added WITH SCHEMABINDING to the CREATE and now get the following error:
我将WITH SCHEMABINDING添加到CREATE，现在出现以下错误：
Msg 10116, Level 16, State 1, Line 2
Cannot create index on view 'DEALMAKER.dbo._Source' because it contains one or more UNION, INTERSECT, or EXCEPT operators. Consider creating a separate indexed view for each query that is an input to the UNION, INTERSECT, or EXCEPT operators of the original view.
My questions are:
我的问题是：
How would I create an index on this view? Would creating separate indexed views really work?
如何在此视图上创建索引？创建单独的索引视图真的有效吗？
Lastly, am I really going to see a performance improvement for any queries that may JOIN this view?
最后，我是否真的会看到可能加入此视图的任何查询的性能提升？
Thanks in advance!
提前致谢！
2 个解决方案
#1
15
You cannot create an index on a view that makes use of a union operator. Really no way around that, sorry!
您无法在使用union运算符的视图上创建索引。真的没办法，对不起！
I would imagine you've seen this, but check out this MSDN page. It gives the requirements for indexed views and explains what they are and how they work.
我想你已经看到了这个，但看看这个MSDN页面。它给出了索引视图的要求，并解释了它们是什么以及它们如何工作。
As to whether or not you'd see a performance benefit if you COULD index the view, that would depend entirely on the size of your tables. I would not expect any impact on creating separate indexed views, as I would assume that your tables are already indexed and you aren't doing any joining or logic in the view.
至于如果你可以为视图编制索引，你是否会看到性能优势，这完全取决于表的大小。我不希望对创建单独的索引视图有任何影响，因为我假设您的表已经编入索引，并且您没有在视图中进行任何连接或逻辑。
#2
16
Why in the WORLD are you using UNION?
你在世界上为什么使用UNION？
With the literals in your SQL there is ZERO chance that you'll have duplicates. So again, why use UNION?
使用SQL中的文字，您将有零重复的机会。再说一次，为什么要使用UNION？
UNION forces a distinct to occur and there's little slower than DISTINCT.
UNION强制发生明显的变化，并且比DISTINCT慢一点。
But since you have something that looks like this:
但是因为你有一些看起来像这样的东西：
SELECT 'A'
UNION
SELECT 'B'
UNION
SELECT 'C'
There's no possibility that you'll ever have duplicates.
你不可能有重复的东西。
Change it to UNION ALL and your query will perform much faster.
将其更改为UNION ALL，您的查询将执行得更快。
This is fundamental SQL - writing a well tuned query is more important than creating view indexes. Start with the basics, understand SQL, tune your query, THEN worry about spending space and slowing DML to improve query speed.
这是基本的SQL - 编写经过良好调优的查询比创建视图索引更重要。从基础开始，了解SQL，调整查询，然后担心花费空间并减慢DML以提高查询速度。
EDIT:
The literals in the query prevent dupes between tables. The only remaining possibility is dupes within a table(s). Since the columns look like PKs and there are no joins that could induce duplication and since the tables all look like lookup tables, what I said is correct. If that assumption isn't true than you may have a legitimate use of UNION without an ALL. However I find that 99% of the time people really meant to use ALL and it's a standard at our company to add a comment to SQL with only UNION because it's so often a mistake. i.e. UNION -- yes i need a distinct list.
查询中的文字可防止表之间的欺骗。唯一剩下的可能性是桌子内的欺骗。由于列看起来像PK，并且没有可能导致重复的连接，因为表格看起来都像查找表，我说的是正确的。如果这个假设不正确，那么你可能合法地使用没有ALL的UNION。但是我发现99％的人真的打算使用ALL，而且我们公司的标准是只用UNION为SQL添加注释，因为它经常是一个错误。即UNION - 是的我需要一份清单。
#1
15
You cannot create an index on a view that makes use of a union operator. Really no way around that, sorry!
您无法在使用union运算符的视图上创建索引。真的没办法，对不起！
I would imagine you've seen this, but check out this MSDN page. It gives the requirements for indexed views and explains what they are and how they work.
我想你已经看到了这个，但看看这个MSDN页面。它给出了索引视图的要求，并解释了它们是什么以及它们如何工作。
As to whether or not you'd see a performance benefit if you COULD index the view, that would depend entirely on the size of your tables. I would not expect any impact on creating separate indexed views, as I would assume that your tables are already indexed and you aren't doing any joining or logic in the view.
至于如果你可以为视图编制索引，你是否会看到性能优势，这完全取决于表的大小。我不希望对创建单独的索引视图有任何影响，因为我假设您的表已经编入索引，并且您没有在视图中进行任何连接或逻辑。
#2
16
Why in the WORLD are you using UNION?
你在世界上为什么使用UNION？
With the literals in your SQL there is ZERO chance that you'll have duplicates. So again, why use UNION?
使用SQL中的文字，您将有零重复的机会。再说一次，为什么要使用UNION？
UNION forces a distinct to occur and there's little slower than DISTINCT.
UNION强制发生明显的变化，并且比DISTINCT慢一点。
But since you have something that looks like this:
但是因为你有一些看起来像这样的东西：
SELECT 'A'
UNION
SELECT 'B'
UNION
SELECT 'C'
There's no possibility that you'll ever have duplicates.
你不可能有重复的东西。
Change it to UNION ALL and your query will perform much faster.
将其更改为UNION ALL，您的查询将执行得更快。
This is fundamental SQL - writing a well tuned query is more important than creating view indexes. Start with the basics, understand SQL, tune your query, THEN worry about spending space and slowing DML to improve query speed.
这是基本的SQL - 编写经过良好调优的查询比创建视图索引更重要。从基础开始，了解SQL，调整查询，然后担心花费空间并减慢DML以提高查询速度。
EDIT:
The literals in the query prevent dupes between tables. The only remaining possibility is dupes within a table(s). Since the columns look like PKs and there are no joins that could induce duplication and since the tables all look like lookup tables, what I said is correct. If that assumption isn't true than you may have a legitimate use of UNION without an ALL. However I find that 99% of the time people really meant to use ALL and it's a standard at our company to add a comment to SQL with only UNION because it's so often a mistake. i.e. UNION -- yes i need a distinct list.
查询中的文字可防止表之间的欺骗。唯一剩下的可能性是桌子内的欺骗。由于列看起来像PK，并且没有可能导致重复的连接，因为表格看起来都像查找表，我说的是正确的。如果这个假设不正确，那么你可能合法地使用没有ALL的UNION。但是我发现99％的人真的打算使用ALL，而且我们公司的标准是只用UNION为SQL添加注释，因为它经常是一个错误。即UNION - 是的我需要一份清单。