使用 UNION 运算符在 SQL 视图上创建索引？它真的会提高性能吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/751838/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create an index on SQL view with UNION operators? Will it really improve performance?
提问by mattruma
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 reallywork?
我将如何在此视图上创建索引？创建单独的索引视图真的有效吗？
Lastly, am I reallygoing to see a performance improvement for any queries that may JOIN this view?
最后，我真的会看到任何可能加入此视图的查询的性能改进吗？
Thanks in advance!
提前致谢！
回答by Adam Robinson
You cannot create an index on a view that makes use of a union operator. Really no way around that, sorry!
您不能在使用联合运算符的视图上创建索引。实在不行，抱歉！
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.
至于如果您可以对视图进行索引，您是否会看到性能优势，这完全取决于您的表的大小。我不希望对创建单独的索引视图有任何影响，因为我假设您的表已经被索引并且您没有在视图中执行任何连接或逻辑。
回答by Adam Robinson
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 并且比 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 mayhave 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——是的，我需要一个不同的列表。
