SQLServerPedia

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.