What
are the differences among/ between @@IDENTITY and SCOPE_IDENTITY() and
IDENT_CURRENT(‘<<tablename>>’) [@@IDENTITY vs SCOPE_IDENTITY()]?
@@IDENTITY
|
SCOPE_IDENTITY()
|
IDENT_CURRENT(‘table’)
|
Returns the last
identity value generated for any table in the current session, across all
scopes
(i.e. current
session and global scope).
|
Returns
the last identity value generated for any table in the current session and
the current scope
(i.e.
current session and local scope).
|
Returns
the last identity value generated for a specific table in any session and any
scope
(i.e.
any session and global scope).
|
CREATE TABLE Parent(RecordId int IDENTITY(1,1))
GO
CREATE TABLE Child(RecordId int IDENTITY(100,1))
GO
/*
-- Trigger definition
CREATE TRIGGER TR_Parent_insert ON Parent FOR INSERT
AS
BEGIN
INSERT Child DEFAULT VALUES
END
GO
-- End of trigger definition
*/
-- Child Procedure definition
CREATE PROCEDURE USP_Child_insert
AS
BEGIN
INSERT
Child DEFAULT VALUES
END
GO
-- End Child Procedure definition
-- Parent Procedure definition
CREATE PROCEDURE
USP_Parent_insert
AS
BEGIN
INSERT
Parent DEFAULT VALUES;
EXEC
USP_Child_insert --
(This SP will
excute in other scope)
SELECT @@IDENTITY;
SELECT SCOPE_IDENTITY();
SELECT IDENT_CURRENT('Parent');
SELECT IDENT_CURRENT('Child');
END
GO
-- End Parent Procedure definition
-- Execute the Procedure
EXEC
USP_Parent_insert
GO
EXAMPLE: 1
RESULT:
SELECT @@IDENTITY; -- Returns 100 i.e. Child.RecordId from Child table.
SELECT SCOPE_IDENTITY(); -- Returns 1 i.e. Parent.RecordId from Parent table.
SELECT IDENT_CURRENT('Parent'); -- Returns 1 i.e. Parent.RecordId from Parent SELECT IDENT_CURRENT('Child'); -- Returns 100 i.e. Child.RecordId from Child table.
EXAMPLE: 2
Execute the following code in same session; you get the results as follows.
SELECT @@IDENTITY; -- Returns 100 i.e. Child.RecordId from Child table.
SELECT SCOPE_IDENTITY(); -- Returns NULL i.e. Parent.RecordId from Parent table.
SELECT IDENT_CURRENT('Parent'); -- Returns 1 i.e. Parent.RecordId from Parent SELECT IDENT_CURRENT('Child'); -- Returns 100 i.e. Child.RecordId from Child
table.
EXAMPLE: 3
Execute the following code in another session; you get the results as follows.
SELECT @@IDENTITY; -- Returns NULL i.e. Child.RecordId from Child table.
SELECT SCOPE_IDENTITY(); -- Returns NULL i.e. Parent.RecordId from Parent table.
SELECT IDENT_CURRENT('Parent'); -- Returns 1 i.e. Parent.RecordId from Parent SELECT IDENT_CURRENT('Child'); -- Returns 100 i.e. Child.RecordId from Child
table.
|
||
No comments:
Post a Comment