If you’re getting an error that reads something like “Cannot create index on view … because its select list does not include a proper use of COUNT_BIG…” and so on in SQL Server, it could be that you’re trying to create an index on a view that uses the GROUP BY clause, but doesn’t have the COUNT_BIG() function.

If the view contains a GROUP BY clause, then it must also have COUNT_BIG(*).

To fix this issue, try adding COUNT_BIG(*) to your SELECT list.

Example of Error

Suppose we create the following view:

CREATE VIEW SalesSummary
WITH SCHEMABINDING
AS
SELECT 
    P.ProductName,
    SUM(S.Quantity) AS TotalQuantity,
    SUM(S.Quantity * S.Price) AS TotalSales
FROM 
    dbo.Sales S
JOIN 
    dbo.Products P
ON 
    S.ProductID = P.ProductID
GROUP BY 
    P.ProductName;

This view will work fine as it is. But if we try to create an index on it, we’ll run into problems.

Let’s try that now:

CREATE UNIQUE CLUSTERED INDEX IDX_SalesSummary ON SalesSummary (ProductName);

Output:

Msg 10138, Level 16, State 1, Line 1
Cannot create index on view 'demo.dbo.SalesSummary' because its select list does not include a proper use of COUNT_BIG. Consider adding COUNT_BIG(*) to select list.

At first, this could be confusing. The error message tells us that our view doesn’t include a “proper use” of COUNT_BIG, but the view doesn’t even have that function!

But that’s actually what SQL Server is trying to get at – we need to add it.

The Microsoft documentation states the following about indexing a view:

If GROUP BY is present, the VIEW definition must contain COUNT_BIG(*) and must not contain HAVING. These GROUP BY restrictions are applicable only to the indexed view definition. A query can use an indexed view in its execution plan even if it doesn’t satisfy these GROUP BY restrictions.

That’s just one requirement of many “additional requirements” that Microsoft lists as part of creating an indexed view.

By the way, the “proper use” would also apply to cases where we did include COUNT_BIG() but we didn’t use it the way SQL Server wants us to. SQL Server specifically wants us to use COUNT_BIG(*) – with the wildcard asterisk (*).

Solution

So the solution is to add COUNT_BIG(*) to the view:

ALTER VIEW SalesSummary
WITH SCHEMABINDING
AS
SELECT 
    P.ProductName,
    SUM(S.Quantity) AS TotalQuantity,
    SUM(S.Quantity * S.Price) AS TotalSales,
    COUNT_BIG(*) AS Count
FROM 
    dbo.Sales S
JOIN 
    dbo.Products P
ON 
    S.ProductID = P.ProductID
GROUP BY 
    P.ProductName;

Here I used ALTER VIEW, but you could alternatively use DROP VIEW followed by CREATE VIEW again.

Either way, we can now create the index against the view:

CREATE UNIQUE CLUSTERED INDEX IDX_SalesSummary ON SalesSummary (ProductName);

Output:

Commands completed successfully.

This time it worked.

It’s worth noting that Microsoft also states:

If the view definition contains a GROUP BY clause, the key of the unique clustered index can reference only the columns specified in the GROUP BY clause.

So you might need to be aware of that too.