SQL Server/T-SQL Tutorial/Sequence Indentity/IDENT CURRENT

Discover what the last IDENTITY value:you can use the IDENT_CURRENT function.

   <source lang="sql">

4> 5> CREATE TABLE MyTable (MyID Int IDENTITY(1, 10) NOT NULL 6> , MyDescription nVarChar(50) NOT NULL) 7> 8> INSERT MyTable (MyDescription) VALUES ("Auto Record 1") 9> INSERT MyTable (MyDescription) VALUES ("Auto Record 2") 10> 11> SET IDENTITY_INSERT MyTable ON 12> 13> INSERT MyTable (MyID, MyDescription) VALUES (5, "Manual Record 1") 14> 15> SET IDENTITY_INSERT MyTable OFF 16> 17> INSERT MyTable (MyDescription) VALUES ("Auto Record 3") 18> 19> SELECT * FROM MyTable 20> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) MyID MyDescription

---

--------------------------------------------------

         1 Auto Record 1
        11 Auto Record 2
         5 Manual Record 1
        21 Auto Record 3

(4 rows affected) 1> 2> SELECT @@IDENTITY AS LastIdentity 3> GO LastIdentity

---

                                     21

(1 rows affected) 1> SELECT IDENT_CURRENT(MyID) AS CurrentIdentity 2> GO Msg 207, Level 16, State 1, Server J\SQLEXPRESS, Line 1 Invalid column name "MyID". 1> 2> 3> drop table MyTable; 4> GO 1> 2> 3> 4></source>

SELECT IDENT_CURRENT("customer")

   <source lang="sql">

2> CREATE TABLE customer 3> ( 4> cust_id smallint IDENTITY(100, 20) NOT NULL, 5> cust_name varchar(50) NOT NULL 6> ) 7> GO 1> 2> SELECT IDENT_CURRENT("customer") 3> GO

---

                                    100

(1 rows affected) 1> 2> drop table customer; 3> GO 1></source>