Sunday, April 8, 2012

Change Primary Key SQL

Change Primary Key in Sql Server 2005 .
Change Primary Key in Sql Server 2008.
Change PK from Clustered to NonClustered in Sql Server.

Just this morning I encountered a problem in my database performance.
It appears like the person who created the table created the primary key as a clustered one, without thinking of what will be in the future and the purpose of the Id PK in the table.

After digging the web, I found this great answer which gave me the hint for the desired solution:

use MyDB

DROP INDEX MyTable.IX_MyTable_FamilyName

ALTER TABLE MyTable
    ADD CONSTRAINT UQ_TableX UNIQUE(Id)

ALTER TABLE MyTable
    DROP CONSTRAINT PK_MyTable

ALTER TABLE MyTable
    ADD CONSTRAINT PK_MyTable PRIMARY KEY NONCLUSTERED(Id)

ALTER TABLE MyTable
    DROP CONSTRAINT UQ_TableX

CREATE NONCLUSTERED INDEX IX_MyTable_FamilyName ON MyTable(FamilyName)




This solves it :)
p.s.
it takes some time to drop and re-index everything.

Elad Shalom,
CTO at ITweetLive.com