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
)
Advertisements

Posted on December 2, 2010, in Sql Server 2008, View. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: