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

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

Msg 1940, Level 16, State 1, Line 3

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

This is a very common error while you are trying to create non clustered index on view. 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 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 tbl_Employee table

create table tbl_Employee

(

EmployeeID int,

name varchar(50),

dept varchar(50),

company varchar(50)

)

GO

--create view on that table

create view dbo.vwEmployee

as

select name,dept,company from dbo.tbl_Employee

--check the view whether it is working

select * from vwEmployee

--create index on view

CREATE nonCLUSTERED INDEX id_View_NCLvwEmployee

ON dbo.vwEmployee (name,dept)

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

--you will get the below error.

Msg 1939, Level 16, State 1, Line 1

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

--reason of above error is, before creating index on view, View should be with schemabindings.

--let us drop view and recreate it.

drop view dbo.vwEmployee

go

create view dbo.vwEmployee

WITH SCHEMABINDING

as

select name,dept,company from dbo.tbl_Employee

GO

--now let us again create index.

CREATE nonCLUSTERED INDEX id_View_NCLvwEmployee

ON dbo.vwEmployee (name,dept)

go

--this time you will again getting different error (as below).

Msg 1940, Level 16, State 1, Line 2

Cannot create index on view 'dbo.vwEmployee'. 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_CLvwEmployee

ON dbo.vwEmployee (name,dept)

GO

--now create nonclustered index

CREATE nonCLUSTERED INDEX id_View_NCLvwEmployee

ON dbo.vwEmployee (company)

--select your view

Select * from vwEmployee