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

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:

The code is as follows:

Copy code

/********* Return the three methods of the last phase ID value ***************/ ***************/ ***************/ USE TestDb2; GO IF OBJECT_ID (N 'tb1 ', N 'u') IS NOT NULL Drop table tb1; GO IF OBJECT_ID (N 'tb2', N 'u') IS NOT NULL Drop table tb2; GO Create table tb1 (id int IDENTITY ); Create table tb2 (id int IDENTITY (100,1 )); GO Create trigger tb1Insert ON tb1 FOR INSERT AS BEGIN INSERT tb2 DEFAULT VALUES END; GO SELECT id FROM tb1; -- At this time, IDs is Empty SELECT id FROM tb2; -- At this time, IDs is Empty Start Session 1: /********* Session 1 ***************/ INSERT tb1 default values; SELECT @ IDENTITY; /* Returns 100 Actually, this is the result returned by the trigger. */ SELECT SCOPE_IDENTITY (); /* Return 1 Before the query, the first Insert statement returns */ SELECT IDENT_CURRENT ('tb2 '); /* Returns 100 Returns the value inserted to tb2, which is inserted by the trigger before query. */ SELECT IDENT_CURRENT ('tb1 '); /* Return 1 Returns the value inserted to tb1, which is inserted by the Insert statement before the query. */ Start new Session 2 /********* Session 2 ***************/ SELECT @ IDENTITY; /* Returns Null because no Insert action is performed before the query. */ SELECT SCOPE_IDENTITY (); /* Returns Null. Because there is no Insert action in the current session in the current scope */ SELECT IDENT_CURRENT ('tb2 '); /* Returns 100 Returns the last inserted value of tb2. */

The current scope 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 "Old Hen", which is always in charge of its own chicks, no matter how it changes outside, it stared at the changes in 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 scope is more like "special city management" for on-site law enforcement. Anyone can handle it, but the power is limited by the current "special content" (that is, scope, it is not a "special content" and cannot be managed. @ IDENTITY is like a "universal city management" around the clock. Nothing can escape its claws, and everything should be taken into your own arms. Do you understand this?