IDENT_CURRENT function in SQL Server

IDENT_CURRENT function will return the last identity value generated by table of any session/scope.

Syntax:

IDENT_CURRENT(‘Table Name’)

Parameters - Table Name is varchar.

Retrun Type – Returns the last identity value of input table. Return data type is Numeric(38,0).

Example:

Below we created a sample table with identity column. So while inserting data we no need to insert id value, it will be taken care by identity column.

CREATE TABLE tblSampleWithIdentity ( ID INT PRIMARY KEY IDENTITY(1,1), Name VARCHAR(10) ) INSERT INTO tblSampleWithIdentity VALUES('A') INSERT INTO tblSampleWithIdentity VALUES('B') INSERT INTO tblSampleWithIdentity VALUES('C') INSERT INTO tblSampleWithIdentity VALUES('D')

Output: SELECT IDENT_CURRENT('tblSampleWithIdentity') -- output is 4 INSERT INTO tblSampleWithIdentity VALUES('E') SELECT IDENT_CURRENT('tblSampleWithIdentity') -- output is 5

Note:If your identity starts with 0 then IDENT_CURRENT returns 0.

CREATE TABLE tblIdentFrom0(ID INT IDENTITY(0,1)) SELECT IDENT_CURRENT('tblIdentFrom0')  -- 0

When IDENT_CURRENT returns NULL:

• If the table does not exists in the database. Eg: Table name ‘myTable’ is not exists in your current database then if you try to execute IDENT_CURRENT(‘myTable’) then it returns NULL value.

•  If table does not have identity column. Then IDENT_CURRENT will return null value.Below script shows a table with out identity column so if you try to execute IDENT_CURRENT it returns null.

Create table tblSampleWithOutIdentity ( ID int primary key, Name varchar(10) ) insert into tblSampleWithOutIdentity values(1,'A') insert into tblSampleWithOutIdentity values(2,'B') select IDENT_CURRENT('tblSampleWithOutIdentity') -- Output is Null

• ·If user doesn’t have permission to access the meta data then IDENT_CURRENT returns NULL value.

Caution: Value generated from IDENT_CURRENT may differ from IDENT_CURRENT + IDENTITY_SEED because of insertions performed by some other sessions.