使用 UNION 运算符在 SQL 视图上创建索引?它真的会提高性能吗?

2021-12-29 00:00:00 sql database sql-server views

我正在尝试在以下视图上创建索引:

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.

至于如果您可以对视图进行索引，您是否会看到性能优势，这完全取决于您的表的大小.我不希望对创建单独的索引视图有任何影响，因为我假设您的表已经被索引并且您没有在视图中执行任何连接或逻辑.

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.

相关文章