Peter Larsson Blog

Patron Saint of Lost Yaks

Bug found in SQL Server 2005 sp2

For this to work, disconnect and reconnect the current query window in SSMS and then copy, paste and run the code below.
Books Online says IDENT_CURRENT "Returns the last identity value generated for a specified table or view in any session and any scope.". So how can the IDENT_CURRENT() return 1 for a newly created table with no inserted records? @@IDENTITY and SCOPE_IDENTITY works as expected.

CREATE TABLE      #Temp
                  (
                        RowID INT IDENTITY(1, 1),
                        theValue INT
                  )
 
SELECT      *
FROM       #Temp
 
SELECT      @@IDENTITY,
            IDENT_CURRENT('#Temp'),
            SCOPE_IDENTITY()
 
INSERT      #Temp
SELECT      99
 
SELECT      *
FROM       #Temp
 
SELECT      @@IDENTITY,
            IDENT_CURRENT('#Temp'),
            SCOPE_IDENTITY()
 
INSERT      #Temp
SELECT      123456
 
SELECT      *
FROM       #Temp
 
SELECT      @@IDENTITY,
            IDENT_CURRENT('#Temp'),
            SCOPE_IDENTITY()
 
DROP TABLE #Temp
Doesn't it seem that IDENT_CURRENT() function bugs out? 

Legacy Comments


Peso
2008-06-05
Answer from Microsoft Connect
You have raised a valid point here, but this has been the behavior since SQL Server 2000 release. Due to backward compatibility reasons, we are preserving this behavior. In SQL Server 2008 we will document this explicitly so that there is no confusion about the behavior of returning a IDENTITY_SEED value for an empty table.

Srini Acharya
Relational Engine