Primary Key VS Clustered Index

Question

Primary Key VS Clustered Index

gupta1282

Default port

Points: 1465
More actions
March 3, 2010 at 12:24 am

#99111

Hi All
I have confusion in Primary Key & Clustered Index
Scenario 1
We have one table and we create primary Key on ID Column
Scenario 2
We have one table and we create clustered Unique Index on ID Column , there is no Primary Key in this table
What is Difference between both and which is better
Thanks
Ghanshyam

Viewing 9 posts - 1 through 8 (of 8 total)

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

They are not mutually exclusive.

A primary key represents a unique key for a record. A clustered index is an index that also controls HOW the data is stored. It is VERY common for the primary key to be the primary key clustered index.

So your question about which is better really can't be answered, they have different purposes.

Also, this better not be homework..

CEWII

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

gupta1282 (3/3/2010)
What is Difference between both and which is better

You need some homework buddy:-)

Read below post

http://www.sqlservercentral.com/Forums/Topic286477-92-1.aspx

http://social.msdn.microsoft.com/forums/en-US/sqltools/thread/086923c4-92c5-4d85-9cea-578dd097b118

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

vidya_pande SSCertifiable Points: 5275 More actions · Answer 3

Unique key can have one Null value in it. Primary key can not have. This is the only difference in this scenario.

I guess u should go with Primary key.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

True, no field in a primary key can be nullable.

CEWII

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 5

A unique (and non-nullable) index is just SQL Server's method of implementing a candidate key. By creating a unique index you in effect create a key constraint as well. However, separation of logical and physical concerns is an important goal of database design and for that reason it is desirable to declare keys explicitly with UNIQUE NOT NULL or PRIMARY KEY constraints rather than rely on unique indexes without those constraints.

It is important to realise that primary keys are not different from other candidate keys. However, it is a very common convention to designate a primary key in every base table even though it makes no logical difference and very little practical difference whether you do or not. Assuming you have at least one candidate key per table the results are the same.

Paul White SSC Guru Points: 150467 More actions · Answer 6

One other small thing: PRIMARY KEY is CLUSTERED by default, UNIQUE constraints are NONCLUSTERED by default.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Tony++ SSCarpal Tunnel Points: 4421 More actions · Answer 7

To add another aspect to the PK vs. clustered discussion:

For .Net development, the DAL (data access layer) generators I've encountered (Subsonic .Net and NetTiers) force a PK -- unique clustered isn't enough -- and DAL generation will fail if there isn't a PK.

If your developers are using DAL generators, consider this during database design and save headaches later.

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

Transactional replication is the same. Primary key required.

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