Return to Home Page

Using The Identity Property

By Craig S. Mullins

The identity property is a very powerful and useful, yet under-utilized feature of Microsoft SQL Server.  It satisfies a common requirement of many applications: the need for a sequential, ascending identifier. Whenever there is a need for a database column to contain a serial number, the identity property can be used to simplify the implementation. The primary benefit of the identity property is that SQL Server does the work to ensure that the number is incremented and inserted properly. But as with all beneficial features of a DBMS, the devil is in the details.  Let’s examine some of those details.

Column Properties

Identity is best classified a column property. Column properties are used by SQL Server to answer the questions:

1. Can this column contain nulls?

2. If nulls are not allowed, should SQL Server increment and insert a value for the column?

There are three basic column properties:

¨                  null - allows nulls in a column. 

¨                  not null - does not allow nulls in a column. This forces the user to assign a specific value to a column (unless a default has been specified). 

¨                  identity - does not allow nulls. Automatically defaults to the next highest number in ascending sequence.

 This article will discuss the identity property only.

How Identity Works

The identity property is used to automatically generate sequential numbers for a column.  A column defined with the identity property is assigned the next sequential number whenever a row is inserted into the table. When data is inserted into the table, a value should not be included for the identity column. Instead, you should use the DEFAULT VALUES option (available with the INSERT statement). This enables SQL Server to generate the next sequential value for the identity column.

Data cannot be inserted directly into an identity column. One column per table can be assigned the identity property.  Additionally, the column must be one of the following data types:

·        tinyint

·        smallint

·        integer

·        decimal

·        numeric(p,0) – the precision is flexible, but the scale must be zero (0)

At the simplest level the identity property generates numbers starting with 1 and incrementing by 1 for each insert. Of course, with database administration, it is always possible to complicate things, and the SQL Server identity function can get a little more complicated.  It is possible to start with a number other than 1 and it is also possible to increment by a number other than 1.  This is done when the table is created using the optional parameters of the identity property.  The identity property accepts two parameters, the first indicating the seed number and the second indicating the increment value.  If no parameters are specified, the default (1,1) is assumed.

To clarify this concept, consider the following three column definitions, for example:

RowId           smallint        identity

OtherId         integer         identity(100)

AnotherId       integer         identity(5,10)

The RowId column will start at 1 and increment by 1; the OtherId column will start at 100 and increment by 1; and the AnotherId column will start at 5 and increment by 10.

Special Situations

Although data cannot be inserted directly into an identity column as a general rule, it is possible to by-pass this rule. It may be necessary to specify a value to the identity column if, for example, a row was accidentally deleted, and the identity value needs to be re-created. To get the last identity value, use the @@identity global variable. This variable is accurate after an insert into a table with an identity column; however, this value is reset after an insert into a table with an identity column occurs. To allow an insert with a specific identity value, use the SET statement to set the IDENTITY_INSERT option ON.

Additionally, if an identity column exists for a table that has frequent deletions, gaps can occur in the sequence because the identity property will not re-generate values that have been used (even if they have been subsequently deleted). If you wish to avoid gaps in sequence at all costs, this may be a valid reason to avoid using the identity property.

To fill a gap in the sequence, you can analyze the existing identity values before explicitly entering one with the IDENTITY_INSERT option ON. Just query the table checking for gaps in the sequence for the identity column. Be sure to take into account the original seed value and the increment value. For example, if the increment value is 2 then there may appear to be gaps in the sequence because SQL Server is counting by 2, not because there are actual gaps.

If the column is referenced and a specific value is provided, then the identity property cannot automatically generate the next sequential value as desired.

Some Identity Advice

Instead of concocting an algorithm to create an ascending key, the identity property is a better choice. Columns assigned the identity property contain system-generated values that can uniquely identify each row within a table. It is automated and requires no additional application coding. However, be aware that it is not a panacea for planning and preparation.  For example, columns assigned the identity property can have repeating values unless a unique index has been created on that column.  This could result from an erroneous insert (instead of letting SQL Server calculate the next value by default).

One additional concern is how to identify which column in the table has been assigned the identity property. It is not necessary to remember—you can simply use the IDENTITYCOL keyword. When referencing data, use the keyword IDENTITYCOL in place of the identity column name. The IDENTITYCOL keyword can be used in an SQL data manipulation statement (SELECT, INSERT, UPDATE, DELETE) to reference an identity column.

SQL Server also enables users to find additional information about the identity property column via two system functions. The IDENT_SEED function returns the seed value specified during creation of an identity column and the IDENT_INCR function returns the increment value specified during creation of the identity column. Both of these may prove useful as you manage and manipulate data in columns assigned the identity property.

Synopsis

The identity property provides a useful function for automatically generating sequential key values. Favor using it instead of alternative, and more complicated, methods.

From SQL Server Update (Xephon) May 1999.

© 1999 Craig S. Mullins, All rights reserved.
Home.