Tuning the Schema - Professional SQL Server 2005 Performance Tuning - page 209

SQL Server provides several paths for enforcing the integrity of the data in a column. Those are: Primary
key constraints, foreign key constraints, unique constraints, check constraints, default definitions,
and nullability.

Primary Key Constraints
A primary key is one or more columns of a table that uniquely identify a row in the table. In some cases
a table may have more than one column that could fulfill this requirement. These are called candidate
keys. In other cases a table may not have any column that uniquely identifies a row in the table.

Another aspect of primary keys is choosing a technique for defining the key. One technique involves
using the existing columns of a table, which is known as a natural key. The other technique involves
inserting a new, independent column in the table whose sole purpose is to provide a unique handle for
all the rows in the table. This is known as a surrogate (or as I call it, artificial) key.

I recommend using surrogate primary keys over natural primary keys for the following reasons:

The primary key in one table generally becomes foreign keys in other tables. Therefore, it's much
easier on the SQL Server environment to have one key column per table.

❑

❑ Surrogate keys usually consist of one column with a small footprint. Natural keys often are

comprised of multiple columns.

❑ Surrogate keys are independent of the other columns. They are not logically linked to the other

columns in a table.

❑ Surrogate key values are immutable. Once a surrogate key value has been generated, it never

has to be changed. In some cases with natural keys, they may need to change. I once worked on
a project that used SSN as a primary key for employees. This key was also a foreign key in 12
other tables. In the case where the SSN had to be changed (and it did occur mainly due to data
entry error) an elaborate and complex update process had to be concocted. All 12 tables and the
employee table had to be updated.

Consider the table structures for an insurance company as shown in Figure 8-14.

Figure 8-14

Note that the Validation table has 6 columns and 5 of them act as the primary key. The sixth column is
a bit column that indicates whether or not the policy coverage has passed its validation requirements.

Now consider the same table structure using surrogate keys instead of natural keys, as shown
in Figure 8-15.