SQL Server/T-SQL Tutorial/Sequence Indentity/SCOPE IDENTITY

Last identity value generated for any table in the current session, for the current scope

5> SELECT SCOPE_IDENTITY()
6>

SELECT SCOPE_IDENTITY()

1> CREATE TABLE customer
2> (
3> cust_id      smallint        IDENTITY(100, 20)  NOT NULL,
4> cust_name    varchar(50)     NOT NULL
5> )
6> GO
1>
2> INSERT customer (cust_name) VALUES ("AAA Gadgets")
3> SELECT SCOPE_IDENTITY()
4> GO
(1 rows affected)
----------------------------------------
                                     100
(1 rows affected)
1>
2> drop table customer;
3> GO

Using the SCOPE_IDENTITY() Function

SCOPE_IDENTITY() returns the last IDENTITY value inserted by your session into any table that has an IDENTITY column, but only in the current scope.
A scope is a batch, a stored procedure, a trigger, or a function.
7>
8> CREATE TABLE MyTable (
9>  key_col int NOT NULL IDENTITY (1,1),
10>  abc     char(1) NOT NULL
11> )
12> INSERT INTO MyTable VALUES ("a")
13> INSERT INTO MyTable VALUES ("b")
14> INSERT INTO MyTable VALUES ("c")
15> SELECT * FROM MyTable ORDER BY key_col
16>
17>
18>
19> DECLARE @mylastident AS int
20> SET @mylastident = SCOPE_IDENTITY ()
21> PRINT @mylastident
22>
23> drop table MyTable
24> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
key_col     abc
----------- ---
          1 a
          2 b
          3 c
(3 rows affected)
3
1>