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 not 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 not 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 (https://blog.sqlauthority.com)
13 Comments. Leave new
Hi Pinal Dev,
I am not understand
altered view then the clusted index is created why ?
what is the reason. could you please explaine any body.
Tx
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
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 :)
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?
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.
So far I did not think about limitation on Union as I did not work much in Views. Great article.
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??!!
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.
well what about if there are two table instead of one .
What if there are two table instead of one , how would use replace OR with UNION then ?
Many years have passed by, but the challanges remain the same. One could try something like that
ALTER VIEW SampleView
WITH SCHEMABINDING
AS
SELECT ID1
, ID2
, SomeData
, STRING_AGG(C.[Source], ‘,’) WITHIN GROUP (ORDER BY [Source] ASC) AS ‘Source’
FROM (
—
SELECT ISNULL(a.ID1, b.ID1) AS ‘ID1’
, ISNULL(a.ID2, b.ID2) AS ‘ID2’
, ISNULL(a.SomeData, b.SomeData) AS ‘SomeData’
, CASE
WHEN a.ID1 IS NOT NULL
THEN ‘a’
ELSE ‘b’
END ‘Source’
FROM dbo.mySampleTable a
FULL JOIN dbo.mySampleTable b
ON 1 = 2
WHERE a.ID1 < 1000
OR b.ID2 < 3000
) c
GROUP BY ID1
, ID2
, SomeData
GO
SELECT *
FROM [SampleView]
ORDER BY ID1