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.
Credit to:
All About IDENTITY Columns in Sql Server 2000
by Anjali Chelawat
Published on 10/5/2006
