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

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

   <source lang="sql">

5> SELECT SCOPE_IDENTITY() 6></source>

SELECT SCOPE_IDENTITY()

   <source lang="sql">

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</source>

Using the SCOPE_IDENTITY() Function

   <source lang="sql">

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></source>