Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : Fixed Server Roles
Error Messages
Home > SQL Server Error Messages > Msg 1902 - Cannot create more than one clustered index on table 'Table Name'.  Drop the existing clustered index 'Clustered Index Name' before creating another.
SQL Server Error Messages - Msg 1902 - Cannot create more than one clustered index on table 'Table Name'.  Drop the existing clustered index 'Clustered Index Name' before creating another.

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.

Related Articles :