Surrogate Versus Natural Keys

Kenneth Fisher digs into the debate on surrogate keys versus natural keys:

A natural key is one constructed of data that already exists in the table. For example using latitude and longitude in a table of addresses. Or the social security number in a table of employees. (Before you say anything, yes, the social security number is a horrible primary key. Be patient.)

My personal preference is to use surrogate keys most of the time and put unique constraints (or unique indexes) on the natural key.  There are some occasions in which I’d deviate, but ceteris paribus I’d pick this strategy..

Related Posts

Column Order Matters For Indexes

Bert Wagner violates Betteridge’s Law of Headlines: When beginning to learn SQL, at some point you learn that indexes can be created to help improve the performance of queries. Creating your first few indexes can be intimidating though, particularly when trying to understand what order to put your key columns in. Today we’ll look at […]

Read More

Creating Indexed Views

Eduardo Pivaral shows how to create a fairly simple indexed view: Views help our query writing by simplifying writing the same sentences and/or aggregations over and over again, but it has a drawback, the views just store our query definition, but the performance is not improved by using them. Since SQL Server 2008, the option […]

Read More

Categories

August 2016
MTWTFSS
« Jul Sep »
1234567
891011121314
15161718192021
22232425262728
293031