Identity value get function in SQL SERVER 2000

server| function

Identity (Identity) column, and many people call it the self-add column, in SQL Server 2000, the identity column is defined by identity, and here is an example of a function related to getting the identity value of the last inserted record

In SQL Server, you can use Scope_identity (), @ @IDENTITY, ident_current () to get the value of the last inserted record, and the difference is:
Scope_identity () returns the last identity value in the identity column inserted into the same scope. One scope is a module--stored procedures, triggers, functions, or batches. As a result, if two statements are in the same stored procedure, function, or batch, they are in the same scope.
@ @IDENTITY returns the last identity value generated in all tables in the current session
Ident_current () returns the identity value that was last generated for the specified table in any session and in any scope
Here 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 ')

/*--Results
Scope_identity () @ @IDENTITY ident_current () for T1 ident_current () for T2
------------------ ------------ -------------------------- -----------------------
1 3 3 1

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

--===========================================
--Test three functions ... 2
--===========================================
INSERT T1 VALUES
SELECT [scope_identity ()]=scope_ IDENTITY (),
    [@ @IDENTITY]=@ @IDENTITY,
    [Ident_current () for T1]=ident_ Current (n ' t1 '),
    [Ident_current () for t2]=ident_current (n ' T2 ')

/*--Results
Scope_identity () @ @IDENTITY ident_current () for T1 ident_current () for T2
------------------ ------------ -------------------------- -----------------------
4 4 4 1

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

--===========================================
--Test three functions ... 3
--* * Opens a new connection, executes 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 ')

/*--Results
Scope_identity () @ @IDENTITY ident_current () for T1 ident_current () for T2
------------------ ------------ -------------------------- -----------------------
NULL NULL 4 &n

--===========================================
--Delete test environment
-- ===========================================
DROP TABLE T1,t2

--B) Code result description
From the above code you can see:
Ident_current () always returns the identity value that was last inserted in the specified table
@ @IDENTITY returns the identity value of the current session, whether or not it is in the same scope, in test 1 and 2, you can see that it returns the identity value of the inserted record in the trigger, and in test 3, the null is returned because the current session has no record inserted
Scope_identity () returns the identity value of the same scope for the current session, so in Test 1 and 2, the value it returns is not affected by the trigger, and in test 3, the null is returned because the current session has no record inserted