Recently, I encountered a table that needed to have the definition of a clustered index altered. It just so happens that the clustered index and the primary key were one and the same, a pretty common occurrence. However, when we went to modify the index, it failed.
The following entry in Books Online for CREATE INDEX explains why:
If the index enforces a PRIMARY KEY or UNIQUE constraint and the index definition is not altered in any way, the index is dropped and re-created preserving the existing constraint. However, if the index definition is altered the statement fails. To change the definition of a PRIMARY KEY or UNIQUE constraint, drop the constraint and add a constraint with the new definition.
Let’s test this, shall we?
/* Create a table with a clustered primary key */ Create Table dbo.myTable ( myID int identity(1,1) Not Null , myDate smalldatetime Not Null , myNumber int Not Null Constraint CIX_myTable Primary Key Clustered (myDate, myID) ); /* Insert some data */ Insert Into myTable Select '2009-01-01', 100 Union All Select '2009-02-01', 200 Union All Select '2009-01-05', 300; /* Try to alter the index - FAIL */ Create Clustered Index CIX_myTable On myTable(myID, myDate, myNumber) With (Drop_Existing = On); /* Drop the clustered primary key */ Alter Table dbo.myTable Drop Constraint CIX_myTable; /* Add a unique clustered index */ Create Unique Clustered Index CIX_myTable On myTable(myDate, myID); /* Add a unique constraint */ Alter Table myTable Add Constraint Unique_myTable Unique (myDate); /* Try to alter the index - SUCCESS */ Create Clustered Index CIX_myTable On myTable(myID, myDate, myNumber) With (Drop_Existing = On); /* Add a primary key constraint */ Alter Table myTable Add Constraint PK_myTable Primary Key (myID, myDate); /* Try to alter the index - SUCCESS */ Create Clustered Index CIX_myTable On myTable(myID, myDate) With (Drop_Existing = On); /* Clean-Up */ Drop Table myTable;
The only instance that actually fails is the PRIMARY KEY constraint. The unique clustered index is able to be modified successfully, even when a unique constraint is applied to the table. So either I’m misunderstanding BOL, or BOL is mistaken. Either way, I’m then left with the following question: is there any reason to actually use a primary key when a unique index serves the same purpose and offers greater flexibility?
Questions, comments, and explanations are welcome. 🙂