SQL Server Returns the three functions of the last IDENTITY value: IDENT_CURRENT, @ IDENTITY, and SCOPE_IDENTITY.

SQL application series in SQL Server 2008-Directory Index

 

Yesterday, someone discussed in the group three functions that SQL Server returned the last IDENTITY value: IDENT_CURRENT, @ IDENTITY, and SCOPE_IDENTITY, and made some markup and summary.

In fact, MSDN has an official explanation for this: all three functions return the final generated id value. However, the "last" scope defined in each of the above functions is different from that defined in sessions.

1. IDENT_CURRENT returns the latest id 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 latest id value generated for a table in the current session across all scopes.

If the statement does not affect any tables that contain the ID column, @ IDENTITY returns NULL. If multiple rows are inserted and Multiple ID values are generated, @ IDENTITY returns the last generated id value. If a statement triggers one or more triggers and the trigger executes the insert operation that generates the id value, the trigger is called immediately after the statement is executed, @ IDENTITY returns the last Identifier value generated by the trigger. If a trigger is triggered after the insert operation is performed on the table that contains the ID column, and the trigger inserts the table that does not have the ID column, then @ IDENTITY returns the id value inserted for the first time. When the INSERT or select into statement fails, the large-capacity copy fails, or the transaction is rolled back, the @ IDENTITY value is not restored to the previous setting.

3. SCOPE_IDENTITY returns the latest id value generated for the current session and a table in the current scope.

SCOPE_IDENTITY only returns the value inserted into the current scope; @ IDENTITY is not limited to a specific scope.

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

Let's look at the example provided by MSDN:

Prepare basic data:

***************//*** *** * ***********/USE TestDb2; GOIF OBJECT_ID (N 'tb1 ', N 'U') is not nulldrop table tb1; GOIF OBJECT_ID (N 'tb2', N 'U') is not nulldrop table tb2; gocreate table tb1 (id int IDENTITY); create table tb2 (id int IDENTITY (); gocreate trigger tb1Insert ON tb1 for insertasbegininsert tb2 default valuesend; GOSELECT id FROM tb1; -- at this time, IDs is EmptySELECT id FROM tb2; -- at this time, IDs is Empty

Start Session 1:

/********** Session 1 ****************/INSERT tb1 default values; SELECT @ IDENTITY; /* returns 100. In fact, this is the result returned by the trigger */SELECT SCOPE_IDENTITY ();/* returns 1 before the query, the first Insert statement returns */SELECT IDENT_CURRENT ('tb2');/* returns the 100 value inserted to tb2, before the query, the trigger inserts */SELECT IDENT_CURRENT ('tb1 ');/* returns 1 to the value inserted into tb1, and is inserted by the Insert statement before the query */

Start new session 2

/********** Session 2 ****************/SELECT @ IDENTITY;/* return Null, because no Insert action */SELECT SCOPE_IDENTITY ();/* returns Null because there is no Insert action in the current session of the current scope */SELECT IDENT_CURRENT ('tb2 '); /* 100 is returned. The last inserted value of tb2 */

Here, I would like to add:Current scopeIt refers to the current SQL statement, current stored procedure, or current trigger.

For better understanding, we can regard the unique IDENT_CURRENT function with parameters as"Lahen", It is always in charge of its own chicks, no matter how the outside changes, it is staring at the changes of its own chicks. SCOPE_IDENTITY and @ IDENTITY without parameters seem to be the "City Management" of law enforcement, and all kinds of "nosy" outside are taken into their own arms, including, from the name, SCOPE_IDENTITY with a range is more like"Special Urban Management", Can be managed by anyone, but the power is limited by the current" special content "(that is, the scope), not" special content ", it cannot be managed. @ IDENTITY is like"Universal Urban Management", Nothing can escape its claws, everything should be taken into your own arms. Do you understand this?

 

Summary:In short,

To obtain the latest Identifier value for a table across any sessions or scopes, use IDENT_CURRENT, which is like a "old hen ".

To obtain the latest identity value for the current session and any table in the current scope, use SCOPE_IDENTITY, which is like a "special city management ".

If you want to obtain the latest IDENTITY value for any table in any scope in the current session, use @ IDENTITY, which is like a "Universal City Management ".