Article Options
Premium Sponsor
Premium Sponsor

 »  Home  »  Data Programming  »  SQL Server  »  All About IDENTITY Columns in Sql Server 2000
All About IDENTITY Columns in Sql Server 2000
by Anjali Chelawat | Published  10/05/2006 | SQL Server | Rating:
Anjali Chelawat
1.5 years of experience in Microsoft Technologies with VB6, ASP, Sql Server 2000. Presently engaged with Aliagroup as a Database and ASP Programmer.  

View all articles by Anjali Chelawat...


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.


IDENTITY [ ( seed , increment ) ]


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.




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.




·               IDENT_CURRENT

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




·               IDENT_SEED

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




·               IDENT_INCR

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



Sponsored Links