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].[tblNames] – where’s the UNIQUE keyword (see below)
(
[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.

UPDATE TO POST

you’ll notice that the clustered index does not include the UNIQUE key word. Although this is acceptable to SQL SERVER, it always assumes that the clustered index is unique (As every record in a clustered index has to be unique) . If  the clustered index has been declared using the UNIQUE keyword then that’s fine. If not – like above – then SQL SERVER will add a 4 byte “uniquifier” into the row (which will be totally transparent to the user). So be aware of this as it could hurt your performance.

See this post for more details.

About Martin Catherall

Martin Catherall is Senior SQL consultant at SQL Down Under, based in Melbourne, Australia. Martin is also a Microsoft Data Platform MVP and Regional Mentor (RM) for the Professional Association for SQL Server. Prior to relocating to Australia he was extremely active in the Christchurch, New Zealand data community - founding the local SQL Server user group and organizing SQL Saturday Christchurch - which later became SQL Saturday South Island. He likes learning interesting stuff about processing and storing data and passing on his knowledge. In his spare time he likes to learn guitar and hang out with his two young sons Callum Glen and Robert Michael.