Database Table Primary Keys

When designing a database, I have a standard of always creating a clustered primary key in each table of type INT IDENTITY(1,1) NOT NULL.

CREATE TABLE Cars(
    ID                    INT IDENTITY(1,1)   NOT NULL
    CONSTRAINT PK_Cars PRIMARY KEY CLUSTERED,
    Brand                 NVARCHAR(20)        NOT NULL,
    RegistrationNumber    NVARCHAR(10)        NOT NULL,
    MadeIn                NVARCHAR(20)        NOT NULL
)

It is simple to create and for 90% of all tables this will give good performance and a small storage footprint. Those 9% of tables that are link tables for many-to-many relationships get a composite primary key, made up of the primary keys from the two related tables. Two simple rules handles 99% of the cases for primary keys. In this post I will go through some considerations for primary keys and show why an INT IDENTITY(1,1) NOT NULL field almost always is a good choice for a clustered primary key. The examples and technical details in this article are written for MS SQL Server, but the concepts should apply to other database engines as well.

• Continue Reading →