Limitation of view in sql server?

SQL SERVER – COUNT(*) Not Allowed but COUNT_BIG(*) Allowed – Limitation of the View 5

September 21, 2010 by pinaldave

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

One of the most prominent limitations of the View it is that it does not support COUNT(*); however, it can support COUNT_BIG(*) operator. In the following case, you see that if View has COUNT (*) in it already, it cannot have a clustered index on it. On the other hand, a similar index would be created if we change the COUNT (*) to COUNT_BIG (*).For an easier understanding of this topic, let us see the example here.

USE tempdb
GOIF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[SampleView]'))DROP VIEW [dbo].[SampleView]
GOIF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID =OBJECT_ID(N'[dbo].[mySampleTable]') AND TYPE IN (N'U'))DROP TABLE [dbo].[mySampleTable]
GO-- Create SampleTableCREATE 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.nameFROM sys.all_objects o1CROSS JOIN sys.all_objects o2
GO-- Create ViewCREATE VIEW SampleViewWITH SCHEMABINDINGAS
SELECT COUNT(*) TableCount, ID2FROM dbo.mySampleTableGROUP BY ID2
GO-- Create Index on ViewCREATE UNIQUE CLUSTERED INDEX [IX_ViewSample] ON [dbo].[SampleView](ID2 ASC)GO/* Above statement will thrown an error
Msg 10136, Level 16, State 1, Line 1
Cannot create index on view "tempdb.dbo.SampleView" because it uses the aggregate COUNT. Use COUNT_BIG instead.
*/
-- Aleter View to replace COUNT with BIG_COUNTALTER VIEW SampleViewWITH SCHEMABINDINGAS
SELECT COUNT_BIG(*) TableCount, ID2FROM dbo.mySampleTableGROUP BY ID2
GO-- Now let us create Index again - this time successfullyCREATE UNIQUE CLUSTERED INDEX [IX_ViewSample] ON [dbo].[SampleView](ID2 ASC)GO

Here is a screenshot of an error that occurred when Views used COUNT(*) and there was an attempt to create an Index on it.

If you are wondering about the reason behind allowing COUNT_BIG and COUNT, here is a quick explanation for this. Itzik Ben-Gan explained me the reason for this situation. He has also clarified the reasons in his book series, ‘Inside T-SQL‘. Here is a concise summary of the explanation. This response from Itzik is produced unaltered and with his permissions:

If the query is a grouped query, SQL Server needs to keep track of the count in each group in order to known whether a group needs to be modified or removed altogether upon DELETE/UPDATE of rows against the underlying tables. As for why the COUNT_BIG and not just COUNT, since SQL Server materializes the counts along with the rest of the view’s data, I guess this has to do with support for groups with more rows than the maximum four-byte integer.

BTW, unrelated to views but along similar lines, see what happens if you add to a clustered table more than the maximum four-byte integer number of rows with the same non-unique clustered index key. The uniqueifiers SQL Server uses internally to distinguish between rows with the same clustered index key is a four-byte integer. Once it overflows, you get error 666 and are not allowed to add more rows with the same clustered index key.

Now, with uniqueifiers for clustering keys I understand the choice to go for four bytes since there are great space savings and therefore read performance benefits as a result, and we are talking about an extreme case for this to happen . But with grouped queries, usually the number of groups is not too large, but groups themselves can be large. Imagine a situation where you try to add more rows to a table that has an indexed view and SQL Server rejects the insert because of a four-byte int overflow in the target group count.

I hope it is clear now. If you want to learn more about this, you can continue reading his book Inside T-SQL.

Let me know what you think of these limitations, as well as your opinions about the example.

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