How to get the next identity with MSSQL?

  • The normal way of getting the identity is to issue: 
select @@identity;
  • @@identity only gets updated after an insert operation.

Data used

  1. USE sql911;
  2. go
  3. SELECT sales_id, sales_date, emp_short
  4. FROM sales02
  5. ORDER BY sales_id;
  6. go
sales_id    sales_date              emp_short
----------- ----------------------- --------------------------------------------------
1           2005-10-06 10:01:01.000 000310
2           2006-10-06 10:01:01.000 000310
3           2005-11-06 10:01:01.000 000110
4           2006-10-06 10:01:01.000 000310
5           2005-12-06 11:01:01.000 000010
6           2006-02-06 13:01:01.000 000190
11          2006-05-06 16:01:01.000 000010
12          2004-10-06 10:01:01.000 000310
13          2005-11-06 10:01:01.000 000010
14          2006-06-06 17:01:01.000 000190
15          2006-01-06 12:01:01.000 000310
16          2006-02-06 13:01:01.000 000110
17          2005-03-06 14:01:01.000 000190
18          2006-10-06 10:01:01.000 000110
19          2006-10-06 10:01:01.000 000110
26          2006-10-06 10:01:01.000 000030
27          2004-03-06 14:01:01.000 000110
28          2005-04-06 15:01:01.000 000010
29          2005-05-06 16:01:01.000 000190
31          2006-07-06 19:01:01.000 000310
32          2006-03-06 14:01:01.000 000310
33          2006-04-06 15:01:01.000 000010
35          2006-06-06 17:01:01.000 000110
41          2007-05-07 11:30:00.000 000030
42          2007-05-08 13:30:00.000 000030
43          2006-07-06 19:01:01.000 000010
44          2007-05-09 14:00:00.000 000110
45          2007-11-06 10:01:01.000 000030
46          2007-05-10 14:00:00.000 000190
47          2007-05-11 14:15:00.000 000190
48          2007-05-12 15:00:00.000 000190
49          2007-05-13 16:00:00.000 000190

(32 row(s) affected)

@@identity

  1. SELECT @@IDENTITY AS 'Last identity used';
  2. go
Last identity used
---------------------------------------
NULL

(1 row(s) affected)
  • No insert operation, therefore @@inserted is null.

IDENT_CURRENT

  1. SELECT IDENT_CURRENT ('sales02') AS 'Last identity used in Sales02';
  2. go
Last identity used in Sales02
---------------------------------------
57

(1 row(s) affected)
  • This looks only at the identity column of sales02.
  • It's different from a max on the identity column.
  1. SELECT MAX(sales_id) AS 'Max sales_id'
  2. FROM sales02;
  3. go
Max sales_id
------------
49
  • IDENT_CURRENT looks at the last identity value for the table selected.
  1. SELECT @@IDENTITY  AS 'Identity';
  2. go
Identity
---------------------------------------
NULL

(1 row(s) affected)
  1. @@identity has the last value used, not the next value, which will be @@identity + 1
  2. ident_current has the last value used, not the next value, which will be ident_current + 1