SQL SERVER – UNION Not Allowed but OR Allowed in Index View – Limitation of the View 6

Update: Please read the summary post of all the 11 Limitations of the view SQL SERVER – The Limitations of the Views – Eleven and more…

If you want to create an Indexed View, you ought to know that UNION Operation is now allowed in Indexed View. It is quite surprising at times when the UNION operation looks very innocent and seems that it cannot be used in the View.

Before an in-depth understanding this subject, let me show you a script where UNION is now allowed in Indexed View:

USE tempdb
GO
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[SampleView]'))
DROP VIEW [dbo].[SampleView]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[mySampleTable]') AND TYPE IN (N'U'))
DROP TABLE [dbo].[mySampleTable]
GO
-- Create SampleTable
CREATE TABLE mySampleTable (ID1 INT, ID2 INT, SomeData VARCHAR(100))
INSERT INTO mySampleTable (ID1,ID2,SomeData)
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY o1.name),
ROW_NUMBER() OVER (ORDER BY o2.name),
o2.name
FROM sys.all_objects o1
CROSS JOIN sys.all_objects o2
GO
-- Create View
CREATE VIEW SampleView
WITH SCHEMABINDING
AS
SELECT
ID1,ID2,SomeData
FROM dbo.mySampleTable
WHERE ID1 < 1000
UNION
SELECT
ID1,ID2,SomeData
FROM dbo.mySampleTable
WHERE ID2 < 1000
GO
-- Create Index on View
CREATE UNIQUE CLUSTERED INDEX [IX_ViewSample] ON [dbo].[SampleView]
(
ID2 ASC
)
GO
/* Above statement will thrown an error
Msg 10116, Level 16, State 1, Line 1
Cannot create index on view 'tempdb.dbo.SampleView' 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.
*/
-- Aleter View to replace COUNT with BIG_COUNT
ALTER VIEW SampleView
WITH SCHEMABINDING
AS
SELECT
ID1,ID2,SomeData
FROM dbo.mySampleTable
WHERE ID1 < 1000 OR ID2 < 1000
GO
-- Now let us create Index again - this time successfully
CREATE UNIQUE CLUSTERED INDEX [IX_ViewSample] ON [dbo].[SampleView]
(
ID2 ASC
)
GO

During the script, the following ERROR would occur if you try to create the index while UNION operation is in the view:

Msg 10116, Level 16, State 1, Line 1
Cannot create index on view ‘tempdb.dbo.SampleView’ 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.

In contrast to this converting the UNION to OR operation would give the same result, plus it would allow you to create an index on the View. Well, our example is one in which we are able to re-write the script with OR clause. However, keep in mind that there can be cases where it is not possible to re-write and you might end up not using Views with Index.

In this series, I have been writing about many limitations and their explanation. Now here are my questions for you:

  • What do you think is the reason behind these limitations?
  • Why UNION is not allowed in the View?

I will publish your answer with due credit on the blog.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

15 thoughts on “SQL SERVER – UNION Not Allowed but OR Allowed in Index View – Limitation of the View 6

  1. The two versions of the view aren’t actually the same. With your sample data they return the same results, but for them to always return the same results you would have to add a DISTINCT to your second version:

    ALTER VIEW SampleView
    WITH SCHEMABINDING
    AS
    SELECT DISTINCT ID1,ID2,SomeData
    FROM dbo.mySampleTable
    WHERE ID1 < 1000 OR ID2 < 1000;
    GO

    Like

    • Very true. I agree.

      As I said at the bottom that in this case only this works. Now try DISTINCT in Index View – it will not work. Another limitation of the VIEW!

      I just think the limitations are endless.

      Kind Regards,
      Michael

      Like

  2. Well, I came up with many reasons but when I started to think about them in depth I figured out ways to work around them. I might guess that there is no technical reason why UNION could not be used in indexed view but I would guess that it would slow down the system so much that it would negate all the positive effects of indexed view.

    IIRC Microsoft has left out some features from Sql Server for performance reasons.

    If you absolute want to use UNION in indexed view I came up with a workaround but it’s a messy one. Create a normal table (with primary key) which is used as “indexed view”. Create trigger(s) to actual data table(s) and do data update “by hand” to the indexed-view-table. Hmmm… I think I just reinvented denormalization :)

    Like

    • Just one more thing. IIRC your can update the data in indexed view with normal insert/update operation. Now because indexed view acts almost like a normal table, which is constructed from other tables, things get impossible if there’s duplicate records in those tables. Which one to update?

      Like

  3. I have to figure one of the reasons that UNION isn’t allowed is because DISTINCT isn’t allowed, and UNION performs a DISTINCT operation automatically. But that isn’t the only reason because a UNION ALL isn’t allowed either.

    Like

  4. As I understand it, it’s about updateability. If you have UNION ALL, SQL Server would need to be able to determine which branch of the UNION statement was affected by an update to one of the underlying tables. My guess is that it is tricky to do so, and therefore UNION ALL is not allowed. UNION (without the all) isn’t allowed for the same reason that DISTINCT isn’t allowed. That’s easier to understand, since it allows a single row in the view to be decomposed from multiple branches of the UNION, so an update to any of the tables could result in extra rows in the views, or fewer. Again, a bit difficult for the query processer to, er, process.

    I haven’t found any definitive answers though, so can you enlighten us??!!

    Like

  5. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Data Warehouseing Concepts – Day 20 of 31 Journey to SQLAuthority

  6. Why could you not use GROUP BY in your indexed view to eliminate duplicates (obviously remembering to include the mandatory COUNT_BIG(*) AS [COUNT]). Is this a hack? I want to put it into production code, so if anyone has any reason to expect risk in doing this, then please let me know…

    Also worked around the lack of a MAX operation in indexed views by using a Tally Table and COUNT_BIG. Have since replaced this terrible hack, in my code, with a TOP (1) WITH TIES … ORDER BY ROW_NUMBER OVER (PARTITION BY ….) operation, as it is not only faster (with the correct indexes), but it doesn’t take the lifetime of an average main sequence star to compute, and it is not a hack.

    Like

  7. Pingback: SQL SERVER – Indexed Views and Restrictions – Quiz – Puzzle – 7 of 31 « SQL Server Journey with SQL Authority

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #008 « SQL Server Journey with SQL Authority

  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #048 | Journey to SQL Authority with Pinal Dave

  10. Pingback: SQL SERVER – Weekly Series – Memory Lane – #049 | Journey to SQL Authority with Pinal Dave

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