I have several tables where records can be uniquely identified with several broad business fields. In the past, I've used these fields as a PK, with these benefits in mind:
- Simplicity; there are no extraneous fields and just one index
- Clustering allows for fast merge joins and range-based filters
However, I've heard a case made for creating a synthetic
IDENTITY INT PK, and instead enforcing the business key with a separate
UNIQUE constraint. The advantage is that the narrow PK makes for much smaller secondary indices.
If a table has no indices other than the PK, I don't see any reason to favor the second approach, though in a large table it's probably best to assume that indices may be necessary in the future, and therefore favor the narrow synthetic PK. Am I missing any considerations?
Incidentally, I'm not arguing against using synthetic keys in data warehouses, I'm just interested in when to use a single broad PK and when to use a narrow PK plus a broad UK.