Creating clustered index on view with COUNT in it

If you are in situation you have a view, but your tables have grown so much ( lets say above 5 million records) soon you will realize that queries that are used in your views consume too much I/O. The solution is to create index on that view.

If you are using COUNT in your view you will run into a problem, something like:

Cannot create index on view “dbo.View” because it uses the aggregate COUNT. Use COUNT_BIG instead.

The solution of this is using COUNT_BIG instead:

ALTER VIEW ViewWITH SCHEMABINDINGAS
SELECT 
COUNT_BIG(*) Count, NameOfProduct
FROM dbo.TableGROUP BY NameOfProduct
GO


CREATE UNIQUE CLUSTERED INDEX [IX_View] ON [dbo].[View](NameOfProduct ASC)GO

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.