Sql Server Error: (Msg 101000) Cannot Create Index on View Because it Contains the DISTINCT Keyword

Creating an indexed view, a common error massage is Msg101000. Yes you cannot use DISTINCT keyword to create an indexed view (or materilized view). It will return error as follow:

Msg 10100, Level 16, State 1, Line 4

Cannot create index on view “YourViewNameHere” 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.

Error itself explains it “How to resolve”.  Consider following query which creates a view using AdventureWorks database and then try to create CLUSTERED INDEX (which will make it an INDEXED VIEW).

USE AdventureWorks

GO

-- Create a schema binded view and use DISTINCT keyword to have unique records

CREATE  VIEW [HumanResources].[vDistinctEmployee]

WITH SCHEMABINDING

AS

SELECT DISTINCT

    e.[EmployeeID]

    ,c.[Title]

    ,c.[FirstName]

    ,c.[MiddleName]

    ,c.[LastName]

    ,sp.[Name] AS [StateProvinceName]

  FROM [HumanResources].[Employee] e

    INNER JOIN [Person].[Contact] c

    ON c.[ContactID] = e.[ContactID]

    INNER JOIN [HumanResources].[EmployeeAddress] ea

    ON e.[EmployeeID] = ea.[EmployeeID]

    INNER JOIN [Person].[Address] a

    ON ea.[AddressID] = a.[AddressID]

    INNER JOIN [Person].[StateProvince] sp

    ON sp.[StateProvinceID] = a.[StateProvinceID];

GO

-- Create clustered index to make in indexed view

CREATE UNIQUE CLUSTERED INDEX [IX_ DistEmployeeID] ON [HumanResources].[vDistinctEmployee]

(

       [EmployeeID] ASC

)

It will generate above mentioned error. Lets resolve it according to given explanation:

USE AdventureWorks

GO

-- Remove DISTINCT keyword and add GROUP BY cluase with an additional column COUNT_BIG(*)

ALTER  VIEW [HumanResources].[vDistinctEmployee]

WITH SCHEMABINDING

AS

SELECT

    e.[EmployeeID]

    ,c.[Title]

    ,c.[FirstName]

    ,c.[MiddleName]

    ,c.[LastName]

    ,sp.[Name] AS [StateProvinceName]

    ,COUNT_BIG(*) AS countbig -- TO RETURN DISTINCT ROWS

  FROM [HumanResources].[Employee] e

    INNER JOIN [Person].[Contact] c

    ON c.[ContactID] = e.[ContactID]

    INNER JOIN [HumanResources].[EmployeeAddress] ea

    ON e.[EmployeeID] = ea.[EmployeeID]

    INNER JOIN [Person].[Address] a

    ON ea.[AddressID] = a.[AddressID]

    INNER JOIN [Person].[StateProvince] sp

    ON sp.[StateProvinceID] = a.[StateProvinceID]

 GROUP BY e.[EmployeeID]

    ,c.[Title]

    ,c.[FirstName]

    ,c.[MiddleName]

    ,c.[LastName]

    ,sp.[Name]

      

GO

-- NOW CREATE UNIQUE CLUSTERED INDEX

CREATE UNIQUE CLUSTERED INDEX [IX_DistEmployeeID] ON [HumanResources].[vDistinctEmployee]

(

       [EmployeeID] ASC

)