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.
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')
Examples and Tips
Example
-- Consider the 2 Tables
CREATE TABLE Student(studId int IDENTITY(1,1), studName varchar(30))
CREATE TABLE Copy_Student(sid int IDENTITY(100,1))
GO
--Create trigger for insert on table Student
CREATE TRIGGER trgStudent ON Student FOR INSERT
AS
BEGIN
INSERT INTO Copy_Student DEFAULT VALUES
END
GO
--Check The Tables
SELECT * FROM Student --No records
SELECT * FROM Copy_Student --No records
GO
--Now do the following to check out the difference
INSERT INTO Student(studName) VALUES('Anjali Chelawat')
SELECT @@IDENTITY AS [IDENTITY]
--Returns the value 100, which was inserted by the trigger that is by the second insert statement.
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
--Returns the value 1, which was inserted by the Student table INSERT Statement before the trigger's insert statement.
SELECT IDENT_CURRENT('student') AS [IDENT_CURRENT]
--Returns last identity value inserted into Student.
SELECT IDENT_CURRENT('copy_student') AS [IDENT_CURRENT]
--Returns last identity value inserted into Copy_Student.
SELECT IDENT_INCR('student') AS [IDENT_INCR]
-- Returns the increment value of the identity column of the table Student. The value provided at the time of creation of the identity column
SELECT IDENT_SEED('student') AS [IDENT_SEED]
-- Returns the seed value of the identity column of the table Student. The value provided at the time of creation of the identity column
A Few Tips
· Allowing inserts to identity columns:
If you are inserting data from some other source to a table with an identity column and you need to ensure you retain the identity values, you can temporarily allow inserts to the identity column. Without doing so explicitly you will receive an error if you attempt to insert a value into the identity column. For example, if I have a table named MYTABLE and I want to allow inserts into the identity column, I can execute the following:
Syntax
set identity_ insert table_name on
Once you execute the command you will be able to insert values into the table's identity column. This will stay in effect in until you turn it off by executing the following:
Syntax
set identity_insert table_name off
Note: Be aware that at any time, only a single table in a session can have the identity_insert set to on. If you attempt to enable this for a table and another table already has this enabled, you will receive an error and will not be able to do so until you first turn this off for the other table. Also, if the value used for the identity is larger than the current identity value then the new value will be used for the identity seed for the column.
· Reseeding the identity value:
You can reseed the identity value, that is, to have the identity values reset or start at a new predefined value by using DBCC CHECKIDENT. For example, if I have a table named MYTABLE and I want to reseed the identity column to 30 I would execute the following:
dbcc checkident (‘table_name’, reseed, 30)
If you wanted to reseed the table to start with an identity of 1 with the next insert then you would reseed the table's identity to 0. The identity seed is what the value is currently at, meaning that the next value will increment the seed and use that. However, one thing to keep in mind is that if you set the identity seed below values that you currently have in the table, that you will violate the identity column's uniqueness constraint as soon as the values start to overlap. The identity value will not just “skip” values that already exist in the table.
Points To Remember
· IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
· @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
· SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.
