Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Primary Keys – why accept the defaults.

G’day,

When I design a table in SQL SERVER, I like to have a reasonable understanding of how the table will be used so that I can implement good indexes up front – rather than waiting until production to performance tune.

Some of those choices will be

  • Where to place the primary key.
  • Where to place the clustered index.

These two attributes are not the same thing, but far too many times I see them implemented as such.

The default in SQL SERVER is to create a primary key as a clustered index (if there is not already a clustered index on the table) – but the default does not have to be accepted.

Even if you are making your primary key the default, then my advice would be to make this obvious in your create scripts – so that anybody coming along later to read your scripts will understand exactly what your intentions were when the scripts were designed.

An example of not accepting the defaults is the script below, which creates the primary key as a non clustered index and also creates a non-unique clustered index on another column – yes, that’s correct, a clustered index does not have to be unique.

/*
Create the table.
*/

CREATE TABLE tblNames

(
Name_ID INT IDENTITY(1,1),
FirstName NVARCHAR(20) NOT NULL,
LastName NVARCHAR(20) NOT NULL
)
GO

/*
Create the clustered index on LastName.
*/

CREATE CLUSTERED INDEX [test] ON [dbo].[tblNames3]
(
[LastName] ASC
);
GO
/*
Create the primary key as a non clustered index
*/

ALTER TABLE tblNames ADD CONSTRAINT PK_tblNames_Name_ID PRIMARY KEY NONCLUSTERED (Name_ID ASC)
GO

Strictly speaking the NONCLUSTERED keyword in the last statement is not necessary as a non clustered primary key would be created anyway – due to the fact that a clustered index already exists on the table – but I always feel it is good to be explicit.

Comments

Posted by Steve Jones on 21 March 2011

I'd agree, the defaults are not necessarily what you want, and you should explicitly list them.

I also would say that you want a narrow clustered key since it's size impacts every other index.

Posted by charlietuna on 22 March 2011

I totally agree that if the PK is going to also be the clustered index, it should be made explicit in the syntax. But I would have maybe liked to see some examples where you would really want to have the two be different - if only because it's so easy to think of examples where you'd want them to be the same.

Posted by lovol on 22 March 2011

We have tables in our system that have data that 'arrive' over time. And later are filtered primarily  on time, as such we always make the clustered index (non unique) on the 'datetime' field, and the primary key ends up with a non-clustered index on the auto id column.

Posted by roger.plowman on 22 March 2011

Hmm.

Given that clustered indexes should be as small as possible, never change, and ultimately should occur in insertion order (to minimize splitting) why would you ever want anything but an Identity as the primary (clustered) key?

Anything else is just begging for fragmentation, isn't it?

Posted by David Moloney on 22 March 2011

You are referring to a surrogate key which may be ideal in some cases but is not "natural".  When you can get a natural key to function like a surrogate, you get the advantages of both at the cost of only one.  A good reference articulating these two choices is here:  

www.sql-server-performance.com/.../natural_surrogate_keys_p1.aspx

Posted by Patrick2525 on 22 March 2011

@Roger... I think it depends on how the data is being used. In the author's example of clustering on the LastName column, so perhaps this table is frequently being used to pull groups of customers by their last name (ie all 'Smith', 'S%', etc), by clustering on this then that reduces the number of pages that must be read from disk.

Posted by Martin Catherall on 22 March 2011

Thanks for all the comments. The main point that I was attempting to make is that you always have options, apart from the defaults. Every case is different and having a choice is a good thing. This is a good article about choosing a key www.sqlskills.com/.../Ever-increasing-clustering-key-the-Clustered-Index-Debateagain!.aspx

Leave a Comment

Please register or log in to leave a comment.