Presents your 
SQL SERVER E-NEWSLETTER for August 1, 2002

<------------------------------------------->

REEVALUATE THE PRIMARY KEY

When your database has several lookup tables, it's wise to reevaluate
the primary key. If you have used a system surrogate key for the primary
key, consider the alternate key as the primary key. For tables that have a
relatively small set of values that don't change much over time,
alternate keys are the best candidates.

An example of this would be if you have a table called CITY_ABBR that
has an identity value as the property of the primary key with a column name
of city_abbr_id. The table may also have a column called city_abbr_cd as
a character datatype with a fixed length of 5 and a unique constraint
associated with the column. The city_abbr_cd column is an alternate key
that is unique to the table. If this table has 1,000 entries and low
volatility to changes, it may be wise to consider the city_abbr_cd as the
primary key instead of city_abbr_id.

The major advantage of this is to reduce the number of joins in queries
in order to help performance. When the city_abbr_cd is all that's needed
from the table, it isn't necessary to join to the table to obtain other
information.

Another advantage to this is that the data is more readable in the child
tables that inherit the primary key of the parent key via the foreign
key column. It's easier for the typical end user to translate character
codes than it is to translate numeric values.

----------------------------------------