liveBook · Manning

Artificial primary key

As explained in 100 SQL Server Mistakes and How to Avoid Them

An artificial primary key is a generated key introduced to identify rows independently of the table's natural business attributes.

Mistake #12 is using a wide natural or composite primary key because it appears to identify rows directly. Wide clustered keys consume index space and memory, forcing SQL Server to read more pages. section 4.2

Use this safer pattern:

  • Add a narrow artificial primary key and populate it automatically with IDENTITY. section 4.2
  • Preserve business-key uniqueness with a unique nonclustered index instead of widening every index. section 4.2
  • Question whether a 16-byte GUID is necessary; NEWID() values can also force clustered indexes to reorder data pages. section 4.2

How 100 SQL Server Mistakes and How to Avoid Them covers it

How deep it goes
Explains index-page storage, IDENTITY-based keys, unique nonclustered indexes, and GUID size and ordering concerns.
Examples
Uses IDENTITY keys, unique natural-key indexes, NEWID() GUIDs, and COTS products that mandate GUID primary keys.
What it assumes
Assumes primary-key and index concepts while distinguishing generated identity from business-attribute uniqueness.
What you can do after
Apply it via SQL Server by choosing a narrow IDENTITY key and separating row identity from natural-key uniqueness.
How it connects
Connects artificial primary keys with natural keys, composite keys, clustered indexes, nonclustered indexes, GUIDs, and SEQUENCE.
Cover of 100 SQL Server Mistakes and How to Avoid Them
liveBookmanning.com
Read 100 SQL Server Mistakes and How to Avoid Them on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of 100 SQL Server Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.