使用UNION运算符在SQL视图上创建索引?它真的会提高性能吗? [英] Create an index on SQL view with UNION operators? Will it really improve performance?
问题描述
我试图在以下视图上创建索引:
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
我得到以下错误:
Msg 1939, Level 16, State 1, Line 2
Cannot create index on view '_Source' because the view is not schema bound.
我添加了WITH SCHEMABINDING到CREATE,现在得到以下错误:
I added WITH SCHEMABINDING to the CREATE and now get the following error:
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.
我的问题是:
我在这个视图上创建一个索引?要创建单独的索引视图(真的)吗?
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?
提前感谢!
推荐答案
您不能在使用联合运算符。真的没有办法,对不起!
You cannot create an index on a view that makes use of a union operator. Really no way around that, sorry!
我想你会看到这个,但看看这个 MSDN页面。它提供了索引视图的要求,并解释了它们是什么以及它们是如何工作的。
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.
至于你是否会看到性能优势,如果你COULD索引视图,这将完全取决于您的表的大小。我不会期望对创建单独的索引视图有任何影响,因为我假设您的表已经建立索引,并且您没有在视图中执行任何连接或逻辑。
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.
这篇关于使用UNION运算符在SQL视图上创建索引?它真的会提高性能吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!