Primary key vs Clustered index

Question

Primary key vs Clustered index

J-440512

SSCertifiable

Points: 6285
More actions
May 27, 2008 at 7:51 am

#380727

I am using the following declaration in a stored procedure:
[font="Courier New"]
CREATE Table #T_Sales_Invoices
(
pkid int PRIMARY KEY IDENTITY NOT NULL,
[/font]
Is there any point in changing it to (underlined)
[font="Courier New"]CREATE Table #T_Sales_Invoices
(
pkid int PRIMARY KEY CLUSTERED IDENTITY NOT NULL,
[/font]
?
This is a temp table that will hold several thousand records and which will be updated several times by the stored procedure.
Or, how does a PRIMARY KEY differ from a CLUSTERED INDEX ? Aren't primary keys sorted the same way as a clustered index?
Regards

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

A primary key is the row's unique 'identifier'. A clustered index defines the physical order of the data. The are unrelated.

By default, when you create a primary key, it's enforced by a clustered index, but there's no reason why you can't make a primary key nonclustered and put the clustered index somewhere else.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

J-440512 SSCertifiable Points: 6285 More actions · Answer 2

This was a quick reply. Thanks. (Even though I did not specify when I "expected" an answer, as posted in the Forum Etiquette topic 😉 )

So I take your answer as no, there is no point in adding the CLUSTERED keyword in my declaration. I really do want the primary key to be clustered. If it is enforced by a clustered index (as I suspected) then this default behaviour does exactly what I need.

Regards

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 3

If it needs to be clustered, always add the "clustered". Defaults can change over time.

J-440512 SSCertifiable Points: 6285 More actions · Answer 4

Forgot about the ever-changing status of defaults.

Thanks