SQL Server Error Messages - Msg 1902¶

Error Message¶

Server: Msg 1902, Level 16, State 3, Line 1
Cannot create more than one clustered index on table 'Table Name'.
Drop the existing clustered index 'Clustered Index Name' before creating another.

Causes:¶

As the message suggests, you can only have one clustered index on a table. A clustered index determines the physical order of data in a table. A clustered index is analogous to a telephone directory, which arranges data by last name. Because the clustered index dictates the physical storage order of the data in the table, a table can contain only one clustered index.

As an example similar to a telephone directory, let’s say you have the following table definition:

CREATE TABLE [dbo].[Directory] (
    [LastName]    VARCHAR(50) NOT NULL,
    [FirstName]   VARCHAR(50) NOT NULL,
    [AreaCode]    CHAR(3),
    [PhoneNumber] CHAR(7)
)
GO

ALTER TABLE [dbo].[Directory]
ADD CONSTRAINT PK_Directory PRIMARY KEY ( [LastName], [FirstName] )
GO

This creates a PRIMARY KEY constraint on the LastName and FirstName columns, which by default is CLUSTERED. Creating another clustered index on the AreaCode and PhoneNumber generates the error:

CREATE CLUSTERED INDEX [IX_Directory_AreaCode_PhoneNumber]
ON [dbo].[Directory] ( [AreaCode], [PhoneNumber] )
GO

Server: Msg 1902, Level 16, State 3, Line 1
Cannot create more than one clustered index on table 'Directory'.
Drop the existing clustered index 'PK_Directory' before creating another.

Solution / Work Around:¶

Since a table can only have one clustered index, you have to create any additional indexes on the table as non-clustered.

CREATE NONCLUSTERED INDEX [IX_Directory_AreaCode_PhoneNumber]
ON [dbo].[Directory] ( [AreaCode], [PhoneNumber] )
GO

If you want to change how the table is clustered, you have to drop the existing clustered index first before you can create a new one.

ALTER TABLE [dbo].[Directory]
DROP CONSTRAINT [PK_Directory]
GO

CREATE CLUSTERED INDEX [IX_Directory_AreaCode_PhoneNumber]
ON [dbo].[Directory] ( [AreaCode], [PhoneNumber] )
GO

ALTER TABLE [dbo].[Directory]
ADD CONSTRAINT [PK_Directory] PRIMARY KEY ( [LastName], [FirstName] )
GO

Since the [dbo].[Directory] already have a clustered index, the PRIMARY KEY constraint defaults to NONCLUSTERED.