CODEWITHSUNDEEP
sqlsql-server
Steven Lemmens
Steven Lemmens

Reputation: 1491

Change clustered index without touching primary key

we have an existing database where we would like to change the clustered index to a unique, monotonically increasing field (as it should have been from the start), but we don't want to change the primary key because there is data referencing this primary key.

We have added a new column SequentialId and populated it with data, to serve as our new clustered index.

But how do we change the clustered index? If possible, we would like to either replace the existing clustered index OR add SequentialId to the current index as the first column.

How do we go about this? It seems we cannot change the clustered index without dropping the primary key (which we can't do).

Upvotes: 0

Views: 369

Answers (1)

Mazhar
Mazhar

Reputation: 3837

Using the ALTER TABLE command drop the PRIMARY KEY constraint, which is not the same as dropping the CLUSTERED INDEX that is enforcing the PRIMARY KEY contraint, and recreate with the additional columns

ALTER TABLE <Table_Name>
DROP CONSTRAINT <constraint_name>

ALTER TABLE <Table_Name>
ADD CONSTRAINT <constraint_name> PRIMARY KEY (<Column1>,<Column2>)

Upvotes: 1

Related Questions