What Is an Identity Column?

An identity column is used in database management to add a generated value to a column in a database table. Unless the database administrator plans the identifying key, because an identity column is not inherently unique, this might cause redundant information. To create the identity field, two numbers are needed; one is for the seed, or primary number, and the second is the increment, which ascends with each new piece of information in the column. Using this column on a database where information is frequently deleted might cause data gaps.

When an identity column is added to a database, it identifies that column with a specific set of numbers, set by either the administrator or the database itself. This makes the database column like a column in a spreadsheet, where each cell has its own identity. The primary use of the identity function is to give the column a unique number, so that the administrator can easily find and call it.

Although the identity column is supposed to help make sections of the database unique, it does not inherently do this. An administrator can assign the same identity numbers to two separate columns, and the database will not return any errors. Commonly, the administrator will find out about this only when he or she calls for one table and gets several in return. To alleviate this, it is best for the administrator to keep notes on what numbers are already in use.

The identity function requires both a seed and an increment, and it specifies the first cell in the column. The seed is the first number and acts as the primary identifying aspect of the entire column, and the increment is the exact cell number in the column. A database administrator can set the identity column numbers in the function, or the database can automatically assign a 1,1 value to the column. If the administrator sets the column as 3,5 for example, then 3 is the seed and 5 is the increment, and the increment will increase with each new cell. This is because the identity function specifies only the first cell, and the database takes over from there.

When combined with a database that has frequently deleted data, an identity column might experience some problems. When the data is deleted, the column will not automatically reassign the numbers, so the column can jump from 3,5 to 3,8 without the other cells being present. By itself, this usually will not cause problems, but can it make organizing the identity function data more difficult.