IDENITY in sql Server

IDENITY is Auto-increment feature of sql server that allows a unique number to be generated when a new record is inserted into a table.

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 StudentId 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 Student

(

StudentId int Identity(1,1) Primary Key,

Name nvarchar(20)

)

In the statements, we only supply values for Name column and not for StudentId column. 

Insert into Student values (‘vinay’)
Insert into Student values (‘jay’)

If I try to execute the following query you will get following error,

Insert into Student values (1,'karan')

 

Error: - An explicit value for the identity column in table 'Student' can only be specified when a column list is used and IDENTITY_INSERT is ON.

 

 

To set identity off write following statement

 

 

SET Identity_Insert Student OFF

 

To set identity on write following statement

 

SET Identity_Insert Student ON

 

 

If you have deleted all the rows in a table, and you want to reset the identity column value, use DBCC CHECKIDENT command

 

DBCC CHECKIDENT(Student, RESEED, 0)

How to get the last generated identity column value in SQL Server

To retrieve the last identity value that is generated. We can use following built in methods

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.

and IDENT_CURRENT('') :- returns the last identity value that is created for a specific table across any session and any scope.

Select SCOPE_IDENTITY()

Select @@IDENTITY

Select IDENT_CURRENT('Student')