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 |