Fix “Cannot create index on view because it contains the DISTINCT keyword” in SQL Server (Error 10100)

by

If you’re getting an error that reads “Cannot create index on view “demo.dbo.vDistinctPrice” because it contains the DISTINCT keyword…” etc etc in SQL Server, it appears that you’re trying to create an index on a view that contains the DISTINCT keyword.

As the error message alludes to, this is not allowed.

To fix this issue, and as the message states, try replacing DISTINCT with GROUP BY or COUNT_BIG(*) to get the same effect. Actually, perhaps it should read “and“, as in “GROUP BY and COUNT_BIG(*)“. I say this because if we include the GROUP BY clause in an indexed view, we must also include COUNT_BIG(*). If we don’t, we get a different error. And it’s not just a matter of including the COUNT_BIG() function with any column name. It must be the asterisk wildcard (*), as in COUNT_BIG(*).

Two other options include omitting DISTINCT from the view altogether (if possible), and just not indexing the view.

Example of Error

Suppose we create a simple view like this:

CREATE VIEW vDistinctPrice WITH SCHEMABINDING AS
    SELECT 
        DISTINCT Price 
    FROM dbo.Sales;

That view contains the DISTINCT keyword.

Now let’s try to create an index against that view:

CREATE UNIQUE CLUSTERED INDEX IDX_DistinctPrice ON vDistinctPrice (Price);

Output:

Msg 10100, Level 16, State 1, Line 1
Cannot create index on view "demo.dbo.vDistinctPrice" because it contains the DISTINCT keyword. Consider removing DISTINCT from the view or not indexing the view. Alternatively, consider replacing DISTINCT with GROUP BY or COUNT_BIG(*) to simulate DISTINCT on grouping columns.

As the message tells us, the DISTINCT keyword is causing a problem.

Solutions

Here are three solutions:

  1. Don’t index the view.
  2. Remove the DISTINCT keyword altogether. But that assumes the query can do without it.
  3. Replace DISTINCT with GROUP BY and COUNT_BIG(*) to get the same effect.

The first two are straight forward and self-explanatory, so let’s look at the third option.

Here’s an example of replacing DISTINCT with the GROUP BY clause along with COUNT_BIG(*) to achieve the same effect that the DISTINCT clause was providing:

ALTER VIEW vDistinctPrice WITH SCHEMABINDING AS
SELECT 
    Price,
    COUNT_BIG(*) AS Count
FROM dbo.Sales
GROUP BY Price;

Now we can create the index:

CREATE UNIQUE CLUSTERED INDEX IDX_DistinctPrice ON vDistinctPrice (Price);

Output:

Commands completed successfully.

This time it worked.

As mentioned, it must be COUNT_BIG(*) with the asterisk wildcard. Also, don’t forget to provide a column alias for the COUNT_BIG(*) column, otherwise you’ll get another error.