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

Thursday, July 23, 2009 |

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

5 comments:

Myriam said...

thanks, it helped me a lot !

Anonymous said...

Good stuff, very helpfull

Anonymous said...

very helpful post, thanks!

Anonymous said...

Perfect! Thank you!

Anonymous said...

Good Job!!