| Craig S. Mullins
Database Performance Management
Using The Identity Property
By Craig S. Mullins
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.
Identity is best classified a column property. Column properties are used by SQL Server to answer the questions:
There are three basic column properties:
- allows nulls in a column.
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).
- does not allow nulls. Automatically defaults to the next highest number in
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:
· 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:
OtherId integer identity(100)
AnotherId integer identity(5,10)
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.
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
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.
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
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.
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.