SCOPE_IDENTITY (TSQL Function)

Returns the last value inserted in the IDENTITY column of a table by INSERT statement in the current scope of the current batch.

Syntax

SCOPE_IDENTITY ( )

Return Types

Returns bigint.

Remarks

When records are inserted into a table with IDENTITY column, this function returns the last value inserted into this column.

If the last insert was done into a table that has no IDENTITY column, this function returns NULL.
So, if you want to keep this value across subsequent INSERT statements, you should save it in a variable.

This value is kept unchanged until the next statement that inserts records into a table.

If no insert in IDENTITY column has been done yet in the current scope, this function returns NULL.

The “scope” is the execution context of a stored procedure, trigger, or user defined function.

This function doesn’t return IDENTITY values generated by stored procedure, trigger, or user defined function (which run in their own “scope”) that may insert rows in other tables, as is the case for @@IDENTITY.

RSQL doesn’t implement stored procedure, trigger, or user defined function, so this problem doesn’t exist and @@IDENTITY returns the same value as SCOPE_IDENTITY.

But to be on the safe side, it is better to always use SCOPE_IDENTITY.

Examples

CREATE TABLE t(a int IDENTITY NOT NULL, b int);
CREATE TABLE u(n int);
GO

PRINT 'Last IDENTITY value is', SCOPE_IDENTITY();  -- returns NULL (no insert done yet in the batch)
INSERT INTO t(b) VALUES (100), (101), (102);
PRINT 'Last IDENTITY value is', SCOPE_IDENTITY();

SELECT * FROM t;
GO

PRINT 'Last IDENTITY value is', SCOPE_IDENTITY();  -- returns NULL (no insert done yet in the batch)
INSERT INTO t(b) VALUES (200), (201);
PRINT 'Last IDENTITY value is', SCOPE_IDENTITY();
INSERT INTO u(n) VALUES (123);                     -- insert into table without IDENTITY column
PRINT 'Last IDENTITY value is', SCOPE_IDENTITY();  -- returns NULL, because of last insert

SELECT * FROM t;

The result is:

2016/03/26 02:16:24 --- executing batch example.sql:10 ---
Last IDENTITY value is <NULL>
(3 row(s) affected)

Last IDENTITY value is 3
a          |b          |
-----------+-----------+
          1|        100|
          2|        101|
          3|        102|

(3 row(s) affected)

2016/03/26 02:16:24 return code: 0

2016/03/26 02:16:24 --- executing batch example.sql:17 ---
Last IDENTITY value is <NULL>
(2 row(s) affected)

Last IDENTITY value is 5
(1 row(s) affected)

Last IDENTITY value is <NULL>   <-- because last insert was into a table without IDENTITY column
a          |b          |
-----------+-----------+
          1|        100|
          2|        101|
          3|        102|
          4|        200|
          5|        201|

(5 row(s) affected)

2016/03/26 02:16:24 return code: 0

Example of Use

This function is very useful if you want to insert parent having IDENTITY column as primary key and children in the same batch:

CREATE TABLE parent (id         INT NOT NULL IDENTITY( 100, 1) PRIMARY KEY,
                     first_name VARCHAR(20),
                     name       VARCHAR(20)
                    );

CREATE TABLE child  (id         INT NOT NULL IDENTITY(5000, 1) PRIMARY KEY,
                     parent_id  INT NOT NULL,
                     first_name VARCHAR(20)
                    );
GO

DECLARE @id BIGINT;

BEGIN TRAN;
INSERT INTO parent (first_name, name) VALUES ('Paul', 'SMITH');
SET @id = SCOPE_IDENTITY();

INSERT INTO child (parent_id, first_name) VALUES (@id, 'John');
INSERT INTO child (parent_id, first_name) VALUES (@id, 'Jack');
INSERT INTO child (parent_id, first_name) VALUES (@id, 'Jill');
COMMIT;

GO

SELECT * FROM parent;
SELECT * FROM child;

The result is:

2016/03/26 03:18:07 --- executing batch example.sql:20 ---
(1 row(s) affected)  <-- Paul SMITH

(1 row(s) affected)  <--     John

(1 row(s) affected)  <--     Jack

(1 row(s) affected)  <--     Jill

2016/03/26 03:18:07 --- executing batch example.sql:57 ---
id         |first_name     |name           |
-----------+---------------+---------------+
        100|Paul           |SMITH          |

(1 row(s) affected)

id         |parent_id  |first_name     |
-----------+-----------+---------------+
       5000|        100|John           |
       5001|        100|Jack           |
       5002|        100|Jill           |

(3 row(s) affected)

2016/03/26 03:18:07 return code: 0

Guide

Reference