When to Choose Logical vs Business vs Composite Primary Keys in SQL Server

Definitions (partial definitions from SQL Server documentation)

Primary Key (PK): a column or set of columns that uniquely identifies each row in a table, enforcing entity integrity.

Foreign Key (FK): a column or set of columns that creates a link between two tables by referencing the primary key of another table.

Clustered Index: stores data rows sorted by the key value; a table can have only one because rows can be stored in only one order.

Non‑clustered Index: contains index key values and row locators pointing to the data; multiple non‑clustered indexes can be created to improve query performance.

Identity Column: an automatically generated numeric column that uniquely identifies each row.

Business (Natural) Key: a column with business meaning used as the primary key.

Logical (Surrogate) Key: a column unrelated to business data used as the primary key.

Composite (Combined) Key: a primary key formed by two or more columns.

Principle Analysis

Logical keys are preferred because business keys may change, causing widespread updates to foreign key references; logical keys isolate such changes to business logic only. Business keys can be large (often strings), leading to higher storage, transmission, and processing costs; using an int or bigint (4‑8 bytes) is more efficient.

Joins on int/bigint foreign keys are faster than joins on string keys. Errors in business key entry (e.g., mistyped codes) propagate to dependent tables, whereas logical keys avoid this issue. However, using business keys can be justified when the data rarely changes, the key is short, and users interact directly with the business field.

Composite keys are useful for relationship tables but can increase storage and indexing overhead; often a surrogate key is a better choice.

Conclusions / Inferences

Avoid business keys when possible; prefer logical keys.

If a business key is used, ensure its value never changes, keep it small, and prevent user edits.

Avoid composite keys except for pure relationship tables.

Best‑Practice Guide for Logical Keys

Choose a data type based on expected row count:

<128 rows – tinyint (1 byte) – rarely practical.

<30 000 rows – smallint (2 bytes) – low frequency.

<2.1 billion rows – int (4 bytes) – fits most cases.

<92 quintillion rows – bigint (8 bytes) – handles very large scales.

≥92 quintillion rows – uniqueidentifier (16 bytes) – suitable for distributed high‑concurrency scenarios.

Use auto‑increment (IDENTITY) or NEWID() for value generation.

Name primary‑key columns as “ ID” for clarity.

For distributed deployments, assign distinct start values with a common step (e.g., start = 1,2,3; step = 10) to keep keys unique across databases.

Consider uniqueidentifier with NEWID() for high‑concurrency or migration needs.

Create a unique index on any business key that must remain unique.

Place a clustered index on the business key only if performance demands; keep the logical key as a simple primary‑key constraint with non‑clustered indexes as needed.

Use composite keys only for pure relationship tables, not for entity tables.