COUNT_BIG in Indexed View

Question

CREATE TABLE test2 ( id INTEGER, name VARCHAR(10), family VARCHAR(10), amount INTEGER)  CREATE VIEW dbo.test2_v WITH SCHEMABINDING  AS SELECT id, SUM(amount) as amount -- , COUNT_BIG(*) as tmp FROM dbo.test2  GROUP BY id  CREATE UNIQUE CLUSTERED INDEX vIdx ON test2_v(id) 

I have error with this code:

Cannot create index on view 'test.dbo.test2_v' because its select list does not include a proper use of COUNT_BIG. Consider adding COUNT_BIG(*) to select list.

I can create view like this:

CREATE VIEW dbo.test2_v WITH SCHEMABINDING      AS     SELECT id, SUM(amount) as amount, COUNT_BIG(*) as tmp     FROM dbo.test2      GROUP BY id 

But I'm just wondering what is purpose of this column in this case?

Answer 1

You need COUNT_BIG in this case because of the fact you are using GROUP BY.

This is one of many limitations of Indexed Views and because of these restrictions, Indexed Views can't be used in many places or the usage of it is NOT as effective as it could have been. Unfortunately, it is how it works currently. Sucks, it narrows the scope of usage.

http://technet.microsoft.com/en-us/library/cc917715.aspx