|
|
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.
|