PHYSICAL ELEMENTS OF DATA MODELS - A Developer's Guide to Data Modeling for SQL Server - page 63

each table that holds the primary key. This is not necessarily a bad thing,
but it means that you must look up the data type and column name when-
ever you want to add another column with a foreign key or you need to
write a piece of code to join tables.
Wouldn't it be nice if all your tables had their PKs in columns having
the same name? For example, every table in your database could be given
a column named objectid and that column could simply have an arbitrary
unique integer. In this case, you can use an identity column in SQL Server
to manage your integer PK value. An identity column is one that auto-
matically increments a number with each insert into the table. When you
make a column an identity, you specify a seed, or starting value, and an in-
crement, which is the number to add each time a new record is added.
Most commonly, the seed and increment are both set to 1, meaning that
each new row will be given an identity value that is 1 higher than the pre-
ceding row.
Another option for an arbitrary PK is a GUID. GUIDs are most often
used as PKs when you need to copy data between databases and you need
to be sure that data copied from another database does not conflict with
existing data. If you were instead to use identities, you would have to play
with the seed values to avoid conflicts; for example, the number 1,000,454
could easily have been used in two databases, creating a conflict when the
data is copied. The disadvantages of GUIDs are that they are larger than
integers and they are not easily readable for humans. Also, PKs are often
clustered, meaning that they are stored in order. Because GUIDs are ran-
dom, each time you add data it ends up getting inserted into the middle of
the PK, and this adds overhead to the operation. In Chapter 10 we talk
more about clustered versus nonclustered PKs.
Of all the PK options we have discussed, we most often use identity
columns. They are easy to set up and they provide consistency across ta-
bles. No matter what method you use, carefully consider the pros and cons.
Implementing a PK in the wrong way not only will make it difficult to write
code against your database but also could lead to degraded performance.
Foreign Keys
As with primary keys, foreign keys in SQL Server work in the same way as
they do in logical design. A foreign key is the column or columns that cor-
respond to a primary key and establish a relationship. Exactly the same
columns with the same data as the primary key exist in the foreign key. It