Home » Topic » Clustered Index Issue In SQL Server 2005

Clustered Index Issue In SQL Server 2005

Andrew Jackson ~ Modified: July 11th, 2015 ~ ~ 1 Minute Reading

Home Forums Clustered Index Issue In SQL Server 2005

Viewing 3 posts - 1 through 3 (of 3 total)
  • Author
    Posts
  • #344 Score: 0
    Andrew Jackson
    Moderator
    1 pt

    I am seeing this Error Msg:
    Server:Msg. 1902‚ Level 16 State3‚ Line1

    Cannot create more than one clustered index on %S_MSG ‘%.*ls’. Drop the existing clustered index ‘%.*ls’ before creating another.

    how can I fix this error?

    #357 Score: 0
    Lincoln Burrows
    Moderator
    16 pts

    You must run the DBCC CHECKDB and find the causes of the error in the error log file otherwise restore from full backup.

    #540 Score: 0
    Stephen West
    Moderator
    4 pts

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

Viewing 3 posts - 1 through 3 (of 3 total)
  • You must be logged in to reply to this topic.