SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Clustered Index is not the Primary Key

By Steve Jones,

I was reading through a list of links for Database Weekly and ran across this script from Pinal Dave, looking for tables where the clustered index isn't the PK. It struck me that this is one of those facts I consider to be so simple, yet I constantly see people confusing. If you click the Primary Key icon in the SSMS/VS designers, or you specify a PK like this:

CREATE TABLE ForSomething
  (
    SomeUniqueVal INT PRIMARY KEY
  );

What will happen is that a clustered index is created on this field by default. It's not the the PK must be clustered, but that SQL Server does this if you don't tell it otherwise. Tables should have primary keys, and while you can debate that, most knowledegable SQL Server people I know want a PK on tables. There are exceptions, but if you can't name them now, use a PK.

However the PK isn't a clustered index. They are separate concepts. The PK can be clustered or non-clustered, and what you choose it up to you. However, I like Kimberly Tripp's advice. Choose the clustering key separately from the PK, keep it unique, fixed, and narrow.. If they're the same, fine, but don't try to make them the same. Choose what works well for your particular table, which means thinking a bit.

You get one clustering key, and it's worth spending five minutes debating the choice with a DBA or developer, or even post a note at SQLServerCentral. Changing the choice isn't hard, but it can interrupt your clients' work on your database, so try to make good design choices early, without blindly accepting defaults. It's worth a few minutes of your time to make a good choice.

 
Total article views: 165 | Views in the last 30 days: 1
 
Related Articles
FORUM

clustered index

clustered index

BLOG

Webcast Follow-Up: Choosing Your Clustered Index

Earlier this month, I presented a session for Pragmatic WorksTraining on the T’s on the selecting th...

FORUM

Clustered Index

SQL Server(clustered Index)

FORUM

cluster index or non cluster index

cluster index or non cluster index

FORUM

Clustered index choice

Hi, I know this subject has been done to death but i find so many conflicting views on picking th...

Tags
editorial    
indexing    
primary key (pk)    
 
Contribute