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.
