SQL Server 2000 identity value Acquisition Function

SQL Server 2000 identity value Acquisition Function

The identity column is also called an auto-incrementing column. in SQL Server 2000, the identity column is defined by identity, the following is an example of a function related to getting the id value of the last inserted record.

In SQL Server, you can use scope_identity (), @ identity, and ident_current () to obtain the value of the last inserted record. The difference is:
Scope_identity () returns the last identity value inserted into the identity column in the same scope. A scope is a module-stored procedure, trigger, function, or batch processing. Therefore, if the two statements are in the same stored procedure, function, or batch processing, they are in the same scope.
@ Identity returns the last Identifier value generated in all tables of the current session.
Ident_current () returns the final id value generated for the specified table in any session and any scope
The following is an example to illustrate their differences.

-- A) Sample Code

-- ===================================================== ====
-- Create a test table
-- ===================================================== ====
Use tempdb
Go

Create Table T1 (ID int identity, Col INT)
Insert T1 select 1
Union all select 2
Create Table T2 (ID int identity, Col INT)
Go

Create trigger tr_insert_t2 on T2
For insert
As
Insert T1 select 3
Go

-- ===================================================== ====
-- Test three functions... 1
-- ===================================================== ====
Insert T2 values (1)
Select [scope_identity ()] = scope_identity (),
[@ Identity] = @ identity,
[Ident_current () for T1] = ident_current (N 't1 '),
[Ident_current () for T2] = ident_current (N 't2 ')

/* -- Result
Scope_identity () @ identity ident_current () for T1 ident_current () for T2
-------------------------------------------------------------------------------
1 3 3 1

(The number of affected rows is 1)
--*/
Go

-- ===================================================== ====
-- Test three functions... 2
-- ===================================================== ====
Insert T1 values (10)
Select [scope_identity ()] = scope_identity (),
[@ Identity] = @ identity,
[Ident_current () for T1] = ident_current (N 't1 '),
[Ident_current () for T2] = ident_current (N 't2 ')

/* -- Result
Scope_identity () @ identity ident_current () for T1 ident_current () for T2
-------------------------------------------------------------------------------
4 4 4 1

(The number of affected rows is 1)
--*/
Go

-- ===================================================== ====
-- Test three functions... 3
-- ** Start a new connection and execute the following code **
-- ===================================================== ====
Select [scope_identity ()] = scope_identity (),
[@ Identity] = @ identity,
[Ident_current () for T1] = ident_current (N 't1 '),
[Ident_current () for T2] = ident_current (N 't2 ')

/* -- Result
Scope_identity () @ identity ident_current () for T1 ident_current () for T2
-------------------------------------------------------------------------------
Null null 4 & n

-- ===================================================== ====
-- Delete the test environment
-- ===================================================== ====
Drop table T1, T2

-- B) code result description

From the code above, we can see that:
Ident_current () always returns the id value inserted at the end of the specified table
@ Identity: return the id value of the current session. No matter whether it is in the same scope or not, in test 1 and 2, you can see that it returns the id value of the records inserted in the trigger, in Test 3, null is returned because there is no insert record for the current session.
Scope_identity () returns the id value of the same scope of the current session. Therefore, in test 1 and 2, the returned value is not affected by the trigger. In Test 3, null is returned because there is no insert record in the current session.