三种方法的比较
IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.
The IDENT_CURRENT function returns NULL when the function is invoked on an empty table.
SQL Server 2005提供的新方法
CREATE TABLE Employees(
Id int IDENTITY(1,1) NOT NULL, -- Other columns
, CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED (Id ASC) )
DECLARE @InsertedRows AS TABLE (Id int)
DECLARE @NewId AS INT INSERT INTO Employees
( /* column names */)
OUTPUT Inserted.Id INTO @InsertedRows
VALUES (/* column values */)
SELECT @NewId = Id FROM @InsertedRows
This code is not only concurrency-safe, but it allows us to get the values of other columns that could have been generated (e.g. by DEFAULTs or TRIGGERs)