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?