Avoid making a wide natural key or composite key the primary key merely because it must be unique. Wide keys consume disk and memory, require more index pages, and make SQL Server read more pages. Use an artificial IDENTITY primary key when appropriate, then enforce natural-key uniqueness with a unique nonclustered index. section 4.2
Natural-key uniqueness without index bloat
A table may use an automatically populated IDENTITY value as its primary key while a unique nonclustered index enforces uniqueness on the natural key. This separates the narrow physical key from the business uniqueness rule. section 4.2
Apply these checks before choosing a wide primary key:
- Ask whether uniqueness is required only within the table or more broadly. section 4.2
- Treat GUID primary keys cautiously because their 16-byte size and random ordering can increase page splits. section 4.2
