April 3, 2010 at 11:14 pm
while creating clustered index on identity column, is a primary key required?
April 4, 2010 at 12:24 am
No, it is not required that the Primary Key is the clustered index. Proof of concept code follows.
Create Table dbo.KeyTest
(
TestId Int Identity(1, 1) Not Null,
TestCode NChar(3) Not Null,
TestDescription NVarChar(40) Not Null,
Constraint [Pk_KeyTest_TestId] Primary Key NonClustered
(
TestId Asc
) On [Primary]
) On [Primary]
Go
Create Unique Clustered Index [Idx_KeyTest_TestCode] On dbo.KeyTest
(
TestCode Asc
) On [Primary];
Go
Insert Into dbo.KeyTest (TestCode, TestDescription)
Select 'Jan', 'January';
Go
Insert Into dbo.KeyTest (TestCode, TestDescription)
Select 'Feb', 'February';
Go
Insert Into dbo.KeyTest (TestCode, TestDescription)
Select 'Mar', 'March';
Go
Insert Into dbo.KeyTest (TestCode, TestDescription)
Select 'Apr', 'April';
Go
Select *
From dbo.KeyTest;
Go
Execute sp_helpindex 'dbo.KeyTest';
Go
Drop Table dbo.KeyTest;
Go
Or if you were asking if you didn't need a primary key on the table at all. I guess technically you don't, but as a personal preference I always like to specify one.
Here's a link for a much more complete explanation than I can give.
April 4, 2010 at 1:05 am
alexjimm (4/3/2010)
while creating clustered index on identity column, is a primary key required?
No. However, IDENTITY columns are generally used as surrogate keys and if you intend the column to be unique then it ought to be created either with a PRIMARY KEY or UNIQUE constraint. Don't assume that an IDENTITY column will be unique unless it has a uniqueness constraint or index on it.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply