IDENTITY in SQL Server

If you haven't heard about $IDENTITY in SQL Server check out the initial part of the scripts explained below to know what it does.

CREATE TABLE IdentityTest
(
  Sno INT IDENTITY(1,2) PRIMARY KEY,
CreatedDate DATETIME
)
GO


INSERT INTO IdentityTest(CreatedDate) 
SELECT Getdate()
Go 10

1. How to display the LAST inserted IDENTITY Value into a table in the current scope and session?

SELECT SCOPE_IDENTITY()

Generally we would use SCOPE_IDENTITY() but since SQL Server 2005 there is a serious bug with this. The safe option to use is OUTPUT clause. Check out this KB article here.

2. What if we don't know the IDENTITY column name but wanted to display its column values? (OR) Without mentioning the IDENTITY column name how to list all the IDENTITY values in a table?

SELECT $IDENTITY FROM IdentityTest

GO

I have seen $IDENTITY working since SQL Server 2005 and above. Not very sure whether it used to work in earlier versions as I have never tried this prior to SQL 2005.

3. How to check the current Identity seed value?
DBCC CHECKIDENT (IdentityTest)
GO

Checking identity information: current identity value '19', current column value '19'.

So while a new row is inserted it would be
"Current Column Value" (19) + STEP Value mentioned in IDENTITY declaration (2)

4. What happens if the transaction fails?

BEGIN TRANSACTION
INSERT INTO IdentityTest(CreatedDate) SELECT Getdate()
ROLLBACK TRANSACTION

Though the transaction is rolled back the "current column value" would have been increased by now! Check out DBCC CHECKIDENT to verity it

DBCC CHECKIDENT (IdentityTest)
GO

Checking identity information: current identity value '21', current column value '21'.

Let's try to INSERT another row into the table.

INSERT INTO IdentityTest(CreatedDate) 
SELECT Getdate()
GO


SELECT $IDENTITY FROM IdentityTest
GO

We can see that after 19 the identity value inserted is 23.

5. Assume we don't want that gap and want to insert a row with identity value 21 manually. How to do it?
--Option1:
--Immediately after the transcation failed may be we could have RESEED it back.
DBCC CHECKIDENT ("dbo.IdentityTest", RESEED, 19);
GO


--Option 2:
SET IDENTITY_INSERT IdentityTest ON

--You need to mentioned the column list else the statement would fail
INSERT INTO IdentityTest (Sno, CreatedDate)
SELECT 21, Getdate()
GO

SET IDENTITY_INSERT IdentityTest OFF

6. How to list all IDENTITY columns in a database?

SELECT 
OBJECT_NAME(OBJECT_ID) AS [Table Name], 
[Name] AS [Identity Column Name],
[seed_value] AS [Seed Value],
[Increment_value] AS [Increment Value],
[Last_Value] AS [Last Value]
FROM 
sys.identity_columns

7. Is it possible to UPDATE a value of an IDENTITY column?

NO. We cannot update an IDENTITY column. If we try to do so it would throw up an error.

SET IDENTITY_INSERT IdentityTest ON


UPDATE IdentityTest SET Sno = 31
WHERE Sno = 21
GO


SET IDENTITY_INSERT IdentityTest OFF

The error message would be something like this:

Msg 8102, Level 16, State 1, Line 4

Cannot update identity column 'Sno'.

8. Is it possible to generate Identity values as NEGATIVE values?

YES it is possible. In the below example it would start from 1 and start decreasing by -1 for each record.

CREATE TABLE IdentityTest_Negative
(
  Sno INT IDENTITY(1,-1) PRIMARY KEY,
  CreatedDate DATETIME
)
GO


INSERT INTO IdentityTest_Negative(CreatedDate) 
 SELECT Getdate()
GO 10


SELECT * FROM IdentityTest_Negative

Related topic written in 2004 - Fetching Identity Value