If a column is
marked as an identity column, then the values for this column are automatically
generated, when you insert a new row into the table. The following, create
table statement marks PersonId as an identity column with seed = 1 and Identity
Increment = 1. Seed and Increment values are optional. If you don’t specify the
identity and seed they both default to 1.
|
Create Table tblPerson
( PersonId int Identity(1,1) Primary Key, Name nvarchar(20) ) |
In the following 2
insert statements, we only supply values for Name column and not for PersonId
column.
|
Insert
into tblPerson values (‘Sam’)
Insert into tblPerson values (‘Sara’) |
If you select all the
rows from tblPerson table, you will see that, ‘Sam’ and ‘Sara’ rows have got 1
and 2 as PersonId.
Now, if I try to
execute the following query, I get an error stating – An explicit value
for the identity column in table ‘tblPerson’ can only be specified when a
column list is used and IDENTITY_INSERT is ON.
|
Insert
into tblPerson values (1,’Todd’)
|
So if you mark a
column as an Identity column, you dont have to explicitly supply a value for
that column when you insert a new row. The value is automatically calculated
and provided by SQL server. So, to insert a row into tblPerson table, just
provide value for Name column.
|
Insert
into tblPerson values (‘Todd’)
|
Delete the row,
that you have just inserted and insert another row. You see that the value for
PersonId is 2. Now if you insert another row, PersonId is 3. A record with
PersonId = 1, does not exist, and I want to fill this gap. To do this, we
should be able to explicitly supply the value for identity column. To
explicitly supply a value for identity column
1. First turn on identity insert – SET Identity_Insert tblPerson ON
2. In the insert query specify the column list
1. First turn on identity insert – SET Identity_Insert tblPerson ON
2. In the insert query specify the column list
|
Insert into
tblPerson(PersonId, Name) values(2, ‘John’)
|
As long as the
Identity_Insert is turned on for a table, you need to explicitly provide the
value for that column. If you don’t provide the value, you get an error
– Explicit value must be specified for identity column in table
‘tblPerson1’ either when IDENTITY_INSERT is set to ON or when a replication
user is inserting into a NOT FOR REPLICATION identity column.
After, you have the gaps in the identity column filled, and if you wish SQL server to calculate the value, turn off Identity_Insert.
After, you have the gaps in the identity column filled, and if you wish SQL server to calculate the value, turn off Identity_Insert.
|
SET Identity_Insert tblPerson OFF
|
If you have deleted
all the rows in a table, and you want to reset the identity column value, use
DBCC CHECKIDENT command. This command will reset PersonId identity column.
|
DBCC CHECKIDENT(tblPerson,
RESEED, 0)
|
How
to get the last generated identity column value in SQL Server
There are several
ways in sql server, to retrieve the last identity value that is generated.
The most common way is to use SCOPE_IDENTITY() built in function.
Apart, from using
SCOPE_IDENTITY(), you also have @@IDENTITY and IDENT_CURRENT(‘TableName’)
function.
Example
queries for getting the last generated identity value
Select SCOPE_IDENTITY()
Select @@IDENTITY
Select IDENT_CURRENT(‘tblPerson’)
Select SCOPE_IDENTITY()
Select @@IDENTITY
Select IDENT_CURRENT(‘tblPerson’)
Let’s
now understand the difference between, these 3 approaches.
SCOPE_IDENTITY()
returns the last identity value that is created in the same session
(Connection) and in the same scope (in the same Stored procedure,
function, trigger). Let’s say, I have 2 tables tblPerson1 and tblPerson2, and I
have a trigger on tblPerson1 table, which will insert a record into
tblPerson2 table. Now, when you insert a record into tblPerson1 table,
SCOPE_IDENTITY() returns the idetentity value that is generated in
tblPerson1 table, where as @@IDENTITY returns, the value that is generated in
tblPerson2 table. So, @@IDENTITY returns the last identity value that is
created in the same session without any consideration to the scope.
IDENT_CURRENT(‘tblPerson’) returns the last identity value created for
a specific table across any session and any scope.
In brief:
SCOPE_IDENTITY() – returns the last identity value that is created in the same session and in the same scope.
@@IDENTITY – returns the last identity value that is created in the same session and across any scope.
IDENT_CURRENT(‘TableName’) – returns the last identity value that is created for a specific table across any session and any scope.
SCOPE_IDENTITY() – returns the last identity value that is created in the same session and in the same scope.
@@IDENTITY – returns the last identity value that is created in the same session and across any scope.
IDENT_CURRENT(‘TableName’) – returns the last identity value that is created for a specific table across any session and any scope.
EmoticonEmoticon