Clustered Index Issue in SQL Server 2005

Question

I am seeing this Error Msg: rnServer: 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?

Edwin J. Hoffer · Accepted Answer

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

Mariya Beckham · Answer

First, you have to create any additional indexes on the table as non-clustered. n nCREATE NONCLUSTERED INDEX [IX_Directory_AreaCode_PhoneNumber]nON [dbo].[Directory] ( [AreaCode], [PhoneNumber] )nGO nnnIf 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. n nALTER TABLE [dbo].[Directory]nDROP CONSTRAINT [PK_Directory]