SQL Server Error Messages - Msg 1940

Error Message

Server: Msg 1940, Level 16, State 1, Line 1
Cannot create index on view '<View Name>'. It does not have a unique clustered index.

Causes

A view is a virtual table whose contents are defined by a query. Like a real table, a view consists of a set of named columns and rows of data. However, a view does not exist as a stored set of data values in a database. The rows and columns of data come from one or more tables referenced in the query defining the view and are produced dynamically when the view is referenced.

Creating indexes on views are allowed as long as the first index created on the view must be a unique clustered index. After the unique clustered index has been created, additional non-clustered indexes can be created. Creating a unique clustered index on a view improves query performance because the view is stored in the database in the same way a table with a clustered index is stored. If the first index being created on a view is not for a unique clustered index, this error message will be raised by SQL Server.

To illustrate, here’s a script that will show how this error message can be encountered:

CREATE TABLE [dbo].[Customer] (
    [CustomerID]          INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    [FirstName]           VARCHAR(50) NOT NULL,
    [LastName]            VARCHAR(50) NOT NULL,
    [Address]             VARCHAR(100) NOT NULL,
    [City]                VARCHAR(50) NOT NULL,
    [State]               CHAR(2) NOT NULL,
    [ZIP]                 VARCHAR(10) NOT NULL
)
GO

CREATE VIEW [dbo].[Customer_NY]
WITH SCHEMABINDING
AS
SELECT [CustomerID], [FirstName], [LastName], [Address], [City], [State], [ZIP]
FROM [dbo].[Customer]
WHERE [State] = 'NY'
GO

CREATE INDEX [IX_Customer_NY_Name] ON [dbo].[Customer_NY] ( [LastName], [FirstName] )
GO

Msg 1940, Level 16, State 1, Line 2
Cannot create index on view 'dbo.Customer_NY'. It does not have a unique clustered index.

Since the [dbo].[Customer_NY] view does not have any previous indexes and the first index being created over the view is a simple index, then this error message is raised by SQL Server.

Solution / Work Around

As mentioned earlier, the first index created on the view must be a unique clustered index. If the uniqueness of the column values in the index being created can be assured, then all that needs to be done is to make the index unique and clustered, as follows:

CREATE UNIQUE CLUSTERED INDEX [IX_Customer_NY_Name] ON [dbo].[Customer_NY] ( [LastName], [FirstName] )
GO

However if the uniqueness of the column values cannot be assured, as there may be a lot of people with the same first name and last name in New York, then the unique clustered index needs to be created on a different column. In the given table definition, the [CustomerID] can be used to create the unique clustered index on the view, as follows:

CREATE UNIQUE CLUSTERED INDEX [IX_Customer_NY] ON [dbo].[Customer_NY] ( [CustomerID] )
GO

The index needs to be both unique and clustered. If only one of this is present, for example just the CLUSTERED part and not the UNIQUE part, SQL Server will generate a different error message:

CREATE CLUSTERED INDEX [IX_Customer_NYa] ON [dbo].[Customer_NY] ( [CustomerID] )
GO

Msg 1941, Level 16, State 1, Line 1
Cannot create nonunique clustered index on view 'dbo.Customer_NY' because only unique clustered indexes are allowed. Consider creating unique clustered index instead.

Now that the view has a unique clustered index, additional non-clustered indexes can now be created. Issuing the CREATE INDEX earlier will now be allowed by SQL Server.

CREATE INDEX [IX_Customer_NY_Name] ON [dbo].[Customer_NY] ( [LastName], [FirstName] )
GO