< goto >SQL Server returns three functions for the last identity value: ident_current, @ @IDENTITY, scope_identity

MSDN to the official explanation: These three functions all return the last generated identity value. However, the "last" scope and the session defined in each of these functions are different.

1. Ident_current returns the most recent identity value generated for a session and the specified table in the current scope.

If the Ident_current value is NULL (because the table never contains rows or has been truncated), the Ident_current function returns a seed value.

2, @ @IDENTITY returns the most recent identity value generated for a table in the current session across all scopes.

If the statement does not affect any table that contains an identity column, the @ @IDENTITY returns NULL. If more than one row is inserted and multiple identity values are generated, the @ @IDENTITY returns the last generated identity value. If the statement triggers one or more triggers, and the trigger performs an insert operation that generates an identity value, it is called immediately after the statement executes, and the @ @IDENTITY returns the last identity value generated by the trigger. If a trigger is triggered after an insert operation on a table that contains an identity column, and the trigger performs an insert operation on another table that does not have an identity column, the @ @IDENTITY returns the identity value that was first inserted. When an INSERT or SELECT into statement fails or a bulk copy fails, or if a transaction is rolled back, the @ @IDENTITY value does not revert to the previous setting.

3. Scope_identity returns the most recent identity value generated for the current session and a table in the current scope.

Scope_identity only returns the value inserted into the current scope, and the @ @IDENTITY is not restricted to a specific scope.

For a more detailed introduction, see MSDN (http://msdn.microsoft.com/zh-cn/library/ms175098.aspx)

Let's look at the examples provided by MSDN:

Prepare the underlying data:

/********* three methods to return the last phase identification value ***************//********* [email protected] invited the month ***************//********* preparing basic data ***************/ UseTestDb2;GOIF object_id(N'tb1'N'U') is  not NULLDROP TABLEtb1;GOIF object_id(N'TB2'N'U') is  not NULLDROP TABLETB2;GOCREATE TABLETB1 (IDint IDENTITY);CREATE TABLETB2 (IDint IDENTITY( -,1));GOCREATE TRIGGERTb1insert onTb1 for INSERT asBEGININSERTTb2DEFAULT VALUESEND;GOSELECTId fromtb1;--at this point, IDs is emptySELECTId fromTB2;--at this point, IDs is emptyStart Session 1:/********* Session 1 ***************/INSERTTb1DEFAULT VALUES;SELECT @ @IDENTITY;/*returns 100 Actually, this is the result of the trigger return*/SELECT scope_identity();/*returns 1 before the query, returned by the first INSERT statement*/SELECTIdent_current ('TB2');/*returns 100 returns the value inserted into the TB2, inserted by the trigger before the query*/SELECTIdent_current ('tb1');/*returns 1 returns the value inserted into the TB1, inserted by the INSERT statement before the query*/start a new session in addition 2/********* Session 2 ***************/SELECT @ @IDENTITY;/*returns NULL because there are no insert actions before the query*/SELECT scope_identity();/*returns NULL because there are no insert actions in the current scope current session*/SELECTIdent_current ('TB2');/*returns 100 returns the last inserted value of TB2*/

< goto >SQL Server returns three functions for the last identity value: ident_current, @ @IDENTITY, scope_identity