About Identity Columns

If you have worked with SQL Server, you are probably familiar with identity columns. These are equivalent to the "AutoNumber" columns in Access. The main purpose of these columns is to provide a primary key to the table when a primary key cannot be defined using other fields in the table.

These columns are like any other column except that their value is not inserted by the user, but by the system itself.

Syntax

IDENTITY [ ( seed , increment ) ]

Where:

seed - Is the value that is used for the very first row that is inserted into the table.

increment- Is the incremental value that is added to the previous identity value and thereby to get new value for the new row that is going to be added.

Note: You must specify both the seed and increment or neither. If neither is specified, the default is (1,1).

A few things you need to know about the identity columns:

·         They should be of data type int, smallint, tinyint, decimal or numeric with scale 0.

·         They cannot contain null values

·         They cannot have any default values

·         The identity increment is an integral value (1, -1, 5, etc.) and cannot contain decimals. Also, it cannot be 0.

·         Identity Seed is 1 by default, and so is the Identity Increment. If you leave the seed field empty, it becomes 0.

Functions associated with IDENTITY column

·               @@IDENTITY
When a record is inserted into a table with an identity column, the function @@IDENTITY returns the last identity value that was inserted in the database.

Syntax

@@IDENTITY

I emphasize the phrase "last identity value" here because this may be different from the identity value of that particular table where the record was inserted.

Why? When a record is inserted and if there is any underlying trigger that modifies other tables, the value can be different. If a trigger adds a record into another table, which happens to have an identity column, @@IDENTITY will now return this new value instead.

·               Scope_IDENTITY

Returns the last IDENTITY value inserted into an IDENTITY column in the same scope. A scope is a stored procedure, function, or batch. Thus, two statements are in the same scope if they are in the same stored procedure, function, or batch. It may be more clear from the example below.

Syntax

SCOPE_IDENTITY()

·               IDENT_CURRENT

Returns the last identity value generated for a specified table in any session and any scope.

Syntax

IDENT_CURRENT('table_name')

·               IDENT_SEED

Returns the seed value specified during the creation of an identity column in a table that has an identity column.

Syntax

IDENT_SEED('table_name')

·               IDENT_INCR

Returns the increment value specified during the creation of an identity column in a table that has an identity column.

Syntax

IDENT_INCR('table_name')