Thursday, December 2, 2010

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
)

No comments:

Post a Comment

All suggestions are welcome