If you’re getting an error that reads something like “Cannot create index on view “demo.dbo.SalesSummary” because it uses the aggregate COUNT. Use COUNT_BIG instead” in SQL Server, it’s because you’re trying to create an index on a view that uses the COUNT() function.

SQL Server doesn’t allow the COUNT() function in indexed views. We must use the COUNT_BIG() function instead.

To fix this issue, replace COUNT() with COUNT_BIG().

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,
    COUNT(*) AS Count
FROM 
    dbo.Sales S
JOIN 
    dbo.Products P
ON 
    S.ProductID = P.ProductID
GROUP BY 
    P.ProductName;

This view works fine as it is, but if we try to create an index on it, we’ll get the error.

Here it goes:

CREATE UNIQUE CLUSTERED INDEX IDX_SalesSummary ON SalesSummary (ProductName);

Output:

Msg 10136, Level 16, State 1, Line 1
Cannot create index on view "demo.dbo.SalesSummary" because it uses the aggregate COUNT. Use COUNT_BIG instead.

As the message tells us, it cannot create an index on our view because of the COUNT() function.

Solution

As mentioned, the solution is to replace COUNT() with COUNT_BIG():

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.

In any case, 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.