TSQL Tutorial : Identity Column

An identity can be thought of as a special form of default value that can be applied to a column in a table. When applied to a numeric column, a new integer value is inserted automatically for each new data row. However, unlike with default constraints, a value cannot normally be specified to override the identity value. In other database management systems an identity may be called an autonumber or automatically incrementing value.

The identity column value is selected based upon the identity specification, which consists of a seed and an increment value. The first row inserted takes the value specified in the seed. Subsequent rows use a number that is calculated by adding the increment value to the previous value. Commonly the seed and increment values are both set to one to give a natural sequence. It is quite acceptable, however, to start with a seed that is negative, potentially doubling the number of available values that may be generated.

An identity can only be added to a single column within any table. Additionally, they can only be added to numeric columns that store integer values. These include TinyInt, SmallInt, Int and BigInt columns. You can also apply an identity to numeric or decimal columns if the scale for the data type is set to zero.

Identity columns are often used as surrogate primary keys as they can be used to generate unique values when used correctly. However, it is possible that an identity can generate a duplicate value if existing rows are within the range of an identity specification. Additionally, you should not rely on identities to provide a sequential set of numbers. It is possible that identity values can be skipped following an aborted or failed insert. Also, the sequence will be broken, leaving holes in the range, if rows are deleted from the table.

Adding an Identity to a Column

There are several ways in which an identity can be applied to a column in a table. In this article we will consider two options: adding identities using the SQL Server Management Studio graphical user interface tools and creating them using Transact-SQL statements.

In the article we will add identity specifications to columns in the JoBS tutorial database. This database has been created over the course of this tutorial. If you have not followed the tutorial, download and run the scripts using the link at the top of this page to create the database and sample data. You can also use this script if you wish to refresh your copy of the database and add items from the previous article.

Adding an Identity Specification Using SQL Server Management Studio

The easiest manner of defining an identity in a database is using the table designer in SQL Server Management Studio (SSMS). An identity can be added to a table when it is first created or at a later time, even if the table already contains information.

The Contracts table in the Jobs database contains an integer-based surrogate primary key that is an ideal candidate for using an identity column. To create this identity, open the table designer for the table and select the first row in the grid. This row represents the ContractNumber column.

Once the row is selected, the column properties will be updated to show, and allow editing of, the column settings. Within the "Table Designer" section you should see the "Identity Specification" option. This option will initially be set to No. Using the + icon to the left of the property, expand the grid to show the three available identity options:

§  (Is Identity). This property determines whether the column uses an identity. When set to No, the two other properties may not be modified.

§  Identity Increment. This value is added to the previous identity value each time a new row is added to the table. The resultant value is stored in the column. The value can be a positive or negative value but may not be zero.

§  Identity Seed. This value is the number that will be used in the identity column for the first row inserted into the table.

In this case, we want the first contract number to be one and we want this number to increase by one each time we insert a new row. To apply these settings, set (Is Identity) to Yes and set both the seed and increment values to 1. To retain the changes, save the table design and close the designer window.