Identification value fetching function in SQL SERVER 2000
In SQL Server 2000, the identity column is defined by IDENTITY, and the following is related to getting the identity value of the last inserted record
An example illustration of the function of off
In SQL Server, you can use SCOPE_IDENTITY(), @@IDENTITY, IDENT_CURRENT() to get the value of the last inserted record, and the difference between them is:
SCOPE_IDENTITY() returns the last IDENTITY value inserted inside the IDENTITY column in the same scope. A scope is a module - stored procedures, triggers, functions
Numbers or batches. Therefore, if two statements are in the same stored procedure, function, or batch, they are in the same scope.
@@IDENTITY Returns the last identification value generated in all tables in the current session
IDENT_CURRENT() returns the last identification value generated for the specified table in any session and any scope
Here's 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 rows affected is 1 row)
--*/
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 rows affected is 1 row)
--*/
GO
-- ===========================================
-- Test three functions: 3
-- ** Open 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
As you can see from the code above:
IDENT_CURRENT() always returns the last identified value inserted in the specified table
@@IDENTITY Returns the identity value of the current session, whether in the same scope or not, in tests 1 and 2, it can be seen that it returns the identity value of the inserted record in the trigger, and in
In Test 3, NULL is returned because there is no insertion record in the current session
SCOPE_IDENTITY() returns the identification value of the same scope of the current session, so in test 1, 2, it returns the value that is not affected by the trigger, and in test 3, because the current session is not interpolated
record, so it returns NULL
|
Posted on 5/5/2015 5:35:51 PM
|
|
|
