Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

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? 

Print | posted on Thursday, June 05, 2008 10:30 AM | Filed Under [ Administration SQL Server 2005 SQL Server 2000 ]

Feedback

Gravatar

# 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
6/5/2008 10:35 AM | Peso
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET