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.
