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. جدول ونتائج الألعاب الأولمبية لعام 2022 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. 🙂
