Home » Blog » 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
  • July 11, 2015 at 12:40 pm #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?

    July 16, 2015 at 11:43 am #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.

    August 31, 2015 at 6:26 am #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.
     

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