SQL Error: Cannot create indexed view 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.

Cannot create indexed view 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.

This error occurs when you try to create an index on a view that contains a UNION, INTERSECT, or EXCEPT operator. For example: the following statement will raise the above error.

http://stackoverflow.com/questions/751838/create-an-index-on-sql-view-with-union-operators-will-it-really-improve-performa

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.

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.

Creating Indexed Views

http://msdn.microsoft.com/en-us/library/ms191432.aspx

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s