Cannot create index on view Msg 1940, Level 16, State 1, Line 1

If you are using View, you might have come across error message something like this:

Msg 1940, Level 16, State 1, Line 1

Cannot create index on view 'ViewName'. It does not have a unique clustered index.

I have seen this question many times in different SQL Server forums so finally decided to write something about this error. If you have view and you want to create Index on that view, that is fine but there is one prerequisite, you have to have one Unique Clustered Index in that view then and then you will be able to create NonClustered Index on that view. You may face this error in any SQL Server version after SQL Server 2000.

Let us see it by example.

--create emp table

create table emps

(

      ID int,

      name varchar(50),

      dept varchar(50),

      company varchar(50)

)

GO

 

--create view on that table

create view dbo.Vemps

as

select name,dept,company from dbo.emps

 

--check the view whether it is working

select * from vemps

--create index on view

CREATE nonCLUSTERED INDEX id_View_Vemps

ON dbo.vemps(name,dept)

--as soon as you will try to create above index,

--you will be greeted by following error.

--Msg 1939, Level 16, State 1, Line 1

--Cannot create index on view 'Vemps' because the view is not schema bound.

--reason of above error is, View should be with schemabindings.

--let us drop view and recreate it.

drop view dbo.vemps

go

create view dbo.Vemps

WITH SCHEMABINDING

as

select name,dept,company from dbo.emps

GO

--now let us again create index.

CREATE nonCLUSTERED INDEX id_View_Vemps

ON dbo.vemps(name,dept)

go

--this time you will be greeted with following error.

--Msg 1940, Level 16, State 1, Line 2

--Cannot create index on view 'dbo.vemps'. It does not have a unique clustered index.

--if you create unique clustered index first and then you will be able to create

--nonclustered index on view

CREATE unique CLUSTERED INDEX id_View_Vemps

ON dbo.vemps(name,dept)

GO

--now create nonclustered index

CREATE nonCLUSTERED INDEX id_View_Vemps_non_clust

ON dbo.vemps(company)

--select your view

select * from vemps

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com