Posts

Showing posts from 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.co