Reply To: Clustered Index Issue In SQL Server 2005

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.