Alter primary key constraint - efficient way

Question

Please let me know the efficient way to alter the primary key constraint in a table(it has millions of records).

Thanks.

Answer 1

Priya,

Your question is not clear. what do you mean by altering primary key constraint. Be specific as to what you want to do?

change the data type? Rename the column? Change the constraint name? Change something with default clustered index created with primary key?

What exactly are you trying to do?

Also which version of SQL are we talking about?

check this link  http://msdn.microsoft.com/en-us/library/ms189251%28v=sql.120%29.aspx

---

Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

Answer 2

It isn't efficient in the way I suspect you want it to be efficient but it's about the only way to go about doing what you need.

1. Drop all foreign keys which reference the primary key.
2. Drop the primary key.
3. Create a new primary key.
4. Add all the foreign keys back which you dropped in step 1 above.

By default SQL Server creates a clustered index on the PK when you setup the PK unless you specifically told SQL Server not to.  So you will likely be rebuilding the entire table.

Answer 3

Do you want to have a NCI on PK instead of CI? You have to drop a constraint.. You know , a  constraint is a logical component  but the index (unique) that SQL Server creates behind the scene is a physical implementation...

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence