BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

SQL Server Identity Columns

The twelfth part of the SQL Server Programming Fundamentals tutorial describes identity columns. By applying an identity specification to a numeric column, the column's value is defaulted to a new value for every row inserted into the table.

What is an Identity?

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.

Inserting Rows

Once the identity value has been applied, you can add new rows to the table using SSMS. Right-click the Contracts table in the Object Explorer and choose "Open Table" from the menu that appears. The table's twenty-one rows will be visible

Note that the numbers in the ContractNumber column are already using the values from one to twenty-one. These values were added manually by the JoBS database script. However, we can now add a new row to see the result of the identity column.

Move the cursor to the blank row at the bottom of the grid. Leave the first cell in this row blank, then add the following values to the remaining cells:

ContractNumberCustomerAddressIdRenewalDateRenewAutomaticallyContractValue
(Leave Blank)5(Leave Blank)(Leave Blank)49.99

After typing all of the information, press the Tab key to move to the next row. You should see that the ContractNumber column is automatically populated. However, sometimes the column is not immediately updated in the grid and incorrectly displays "NULL". If this is the case, refresh the grid by pressing Ctrl-R. You will see that SQL Server has recognised that some of the identity range has already been used and has therefore set the contract number for the new row to twenty-two.

You can, of course, use Transact-SQL statements to insert data into the table. When creating new rows, you should not attempt to insert a value into the identity column, as this will cause an error.

10 September 2008