Identity Columns

Using this property we can create an identity column in a table, An Identity column is a auto incrementing column. A table can have only one Identity column. SQL Server will automatically take care of identity columns. By default the column value will increment by 1, if require we can modify the increment value.

Identity (seed, increment) -- Here seed is the starting value and increment is the growth value.

If neither is specified then it treat as identity (1,1).

Example

CREATE TABLE TestIdentity (Id INT IDENTITY(1,1), NAME VARCHAR(20))

  

If you start inserting rows into the above table then it starts from 1 and increment by 1 from next row onwards.

INSERT INTO TestIdentity VALUES ('Kalyan')INSERT INTO TestIdentity VALUES ('Kishore')
By using DBCC CHECKIDENT command we can verify the current identity value of a table.

DBCC CHECKIDENT(TestIdentity) 
 

The disadvantage of identity property is, if you remove any row from a table then that gap will remains same.

If you want to insert value into identity column manually use the below command Identity_Insert on tablename, It will allow us to insert value into identity field  

SET IDENTITY_INSERT testIdentity ONINSERT
INTO TestIdentity (ID, NAME) VALUES (7, 'Mallik')SET IDENTITY_INSERT testIdentity OFF 
How to find last inserted identity value, SQL Server provides three methods to retrieve the value.
SELECT @@identity -- Returns last identity value irrespective of any table in the current session.SELECT SCOPE_IDENTITY() -- Returns last identity value irrespective of any table in the current scope.SELECT IDENT_CURRENT('TestIdentity') -- Returns the last identity value of the specific table