Clustered Index Change

Is there an easy way to alter an identity column which is the clustered index and primary key on a table so that it is no longer clustered? I am going to create a new clustered index but the current one is difficult to alter.

Thanks in advance.

Drop the existing primary key constraint and re-create it again.

Code Snippet

Create table IndexChange

(

Id int identity(1,1) constraint IndexChange_PK primary key clustered,

Name varchar(100)

)

Alter Table IndexChange Drop Constraint IndexChange_PK

Alter Table IndexChange Add Constraint IndexChange_PK Primary Key nonclustered (id)

|||Thanks Manivannan. I was hoping I could alter the index because it is a foreign key constraint on other tables. I can't just drop it because of the references. Is there a way to do it without removing the contraints?|||

There is not a way to 'convert' an index from custered to non-clustered WITHOUT dropping it and re-creating it.

You may have to create a script that

drops all of the FK constraints

drops all of the other indexes on the table

drops the PK constraint

re-creates the PK constraint non-clustered

re-creates the other indexes on the table

re-creates the FK constraints

Dropping and later re-creating the other indexes on the table should greatly increase the speed of the process.

|||

That explains why I couldn't find information on altering the index to remove the cluster.

I'll drop the FK constraints and the indexes and recreate them. No easy way out.

Thanks Arnie!