Function for retrieving the id value in SQL Server 2000 !!!!

identity) column, which is also called an auto-increment 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

SQL Server, you can use scope_ I Dentity (), @ identity, ident_current () to get 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 id value generated in all tables of the current session
ident_current () returns the final Identifier value generated for any session and specified table in any scope.
The following is an example to illustrate their differences.

-- A) Example 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

-- ============================================ =====< br> -- test three functions .. 1
-- ======================================== ==========< br> 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

-- ============================================ ========< br> -- test three functions .. 2
-- ======================================== ==========< br> 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

-- ============================================ ========< br> -- test three functions .. 3
-- ** enable a new connection, run the following code **
-- ================================ ===============< br> 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 4 & n

-- ============================================ ========< br> -- delete the test environment
-- ======================== =============================< br> drop table T1, t2

-- B) code result description

from the code above, you can see:
ident_current () always returns the id value inserted at the end of the specified table
@ identity returns the id value of the current session, regardless of whether it is in the same scope, in test 1 and 2, we can see that it returns the id value of the records inserted in the trigger. In Test 3, because there is no insert record in the current session, therefore, return NULL
scope_identity () to return 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.