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.