Use and management of identity columns in SQL Server

When designing a database to support applications is essential to consider how to handle primary keys. There are in this regard at least two schools of thought: one that argues that the right thing to do is to use the so-called surrogate keys (ie not based on real data) and another that claims that must be used instead of real data as key values. Then there is a middle path which consists in 'a database for use within both of these solutions, depending on the relative pros and cons (which we will soon).

When designing a table it usually contains one or more columns that make up its primary key. As we know a table's primary key is a value (or a combination of values) that uniquely identifies each row. As mentioned previously, if a key is made up of real values ​​is called natural key, but if for example the key is generated every time you insert a row in the table it is called surrogate key. A surrogate key is usually a numeric value in SQL Server, and often the columns of this type are those of identity, of which more later.

A natural key is made up of real data, ie data that have a relationship with values ​​in other columns of the row (eg the tax code of an individual in a Customers table that also contains its generality). Even a surrogate key uniquely identifies a row in a table but its value has no relation to other values ​​of the line and it is simply generated and stored.

We analyze the pros and cons of the two types of keys beginning with the surrogate:

The PRO

• A surrogate key has no relations with the other data line
• If you need to make changes to the database regarding the updating of the natural keys that can easily be done without compromising the foreign key relationships, if the latter are not based on natural keys but on a surrogate
• The surrogate keys are usually integer value and therefore require only four bytes to store in this way making structures smaller performance index (which has a positive effect of join operations)

AGAINST THE

• If the linked tables in foreign key with a value surrogate is linked to a main table, to obtain the actual values ​​of connection between the various tables you must join operations
• The surrogate key is not very useful when searching for specific information, as the values ​​contained in them have no real meaning

As for the natural keys:

The PRO

• Lend themselves to research because the values ​​have real meaning
• Require fewer steps to get the join key values ​​because they are contained in all tables involved in joins
• Lend themselves to research because the values ​​have real meaning

AGAINST THE

• It 'much more complicated update, especially if the foreign key relationships with other tables are based on them
• Indexes assume bigger because natural keys typically require more bytes to store
• Joins based on composite natural keys (which often include string data) are slower than those made with surrogate keys

• <<
• 1
• 2
• 3
• >>