6 Different Ways To Get The Current Identity Value in SQL

Ravi Tuvar

3.22/5 (7 votes)

5 Nov 2011CPOL

49.4K

Identity Value

SQL

CREATE TABLE TestOne (id INT IDENTITY,SomeDate DATETIME)
CREATE TABLE TestTwo (id INT IDENTITY,TestOneID INT,SomeDate DATETIME)

  INSERT TestOne VALUES(GETDATE())
  INSERT TestOne VALUES(GETDATE())
  INSERT TestOne VALUES(GETDATE())
  INSERT TestOne VALUES(GETDATE())

@@IDENTITY

SQL

SELECT @@IDENTITY

DBCC CHECKIDENT

SQL

DBCC CHECKIDENT (TestOne, NORESEED)

MAX FUNCTION

SQL

SELECT MAX(id) FROM TestOne

TOP 1 AND ORDER BY DESC

SQL

SELECT TOP 1 id FROM TestOne ORDER BY id DESC

IDENT_CURRENT

SQL

SELECT IDENT_CURRENT('TestOne')

SCOPE_IDENTITY

SQL

SELECT SCOPE_IDENTITY()

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)