What the missing numbers in an identity column with MSSQL

Identity columns provide automatic, almost incremental, numbering. You can only have 1 identity column per table.

Should be an int, smallint, tinyint, decimal or numeric with scale 0.
Can't contain nulls.
Can't have any default.
The increment is an integer value (1, 10,...) and can not contain decimals. Also, it can't be a 0 increment.
Each value is unique.
There is no guarantee that it's a consecutive increment, even if the increment is 1.
Identity number are NEVER reused, even if somebody delete rows or even the last row.
NEVER use identity for invoice numbers, cheque numbers.

Data used

USE sql911;
go
 
SELECT sales_id, sales_date, sales_qty, emp_short 
FROM sales02;
go

sales_id    sales_date              sales_qty                               emp_short
----------- ----------------------- --------------------------------------- --------------------------------------------------
1           2005-10-06 10:01:01.000 10.00                                   000310
2           2006-10-06 10:01:01.000 10.00                                   000310
3           2005-11-06 10:01:01.000 9.00                                    000110
4           2006-10-06 10:01:01.000 10.00                                   000310
5           2005-12-06 11:01:01.000 8.00                                    000010
6           2006-02-06 13:01:01.000 6.00                                    000190
11          2006-05-06 16:01:01.000 3.00                                    000010
12          2004-10-06 10:01:01.000 10.00                                   000310
13          2005-11-06 10:01:01.000 9.00                                    000010
14          2006-06-06 17:01:01.000 2.00                                    000190
15          2006-01-06 12:01:01.000 7.00                                    000310
16          2006-02-06 13:01:01.000 6.00                                    000110
17          2005-03-06 14:01:01.000 5.00                                    000190
18          2006-10-06 10:01:01.000 10.00                                   000110
19          2006-10-06 10:01:01.000 3.00                                    000110
26          2006-10-06 10:01:01.000 10.00                                   000030
27          2004-03-06 14:01:01.000 5.00                                    000110
28          2005-04-06 15:01:01.000 4.00                                    000010
29          2005-05-06 16:01:01.000 3.00                                    000190
31          2006-07-06 19:01:01.000 1.00                                    000310
32          2006-03-06 14:01:01.000 5.00                                    000310
33          2006-04-06 15:01:01.000 4.00                                    000010
35          2006-06-06 17:01:01.000 2.00                                    000110
41          2007-05-07 11:30:00.000 2.00                                    000030
42          2007-05-08 13:30:00.000 1.00                                    000030
43          2006-07-06 19:01:01.000 1.00                                    000010
44          2007-05-09 14:00:00.000 1.25                                    000110
45          2007-11-06 10:01:01.000 9.00                                    000030
46          2007-05-10 14:00:00.000 5.00                                    000190
47          2007-05-11 14:15:00.000 4.00                                    000190
48          2007-05-12 15:00:00.000 2.00                                    000190
49          2007-05-13 16:00:00.000 1.00                                    000190

(32 row(s) affected)

Missing identity numbers in MSSQL

There are many ways of getting a list of the missing numbers in MSSQL.

My favorite is a number table. I personally use the number table for numbers but also for wording the cheque amounts.
A simple self link will only give the first number that is missing, even if the gap is more than 2 numbers.

SELECT nums 
FROM num
WHERE num < 10;
go

num
-----------
1
2
3
4
5
6
7
8
9

(9 row(s) affected)

SELECT num AS 'Missing sales_id'
FROM nums
WHERE num not in (
  SELECT sales_id  FROM sales02)
  and num <= (SELECT MAX(sales_id) FROM sales02);
go

Missing sales_id
----------------
7
8
9
10
20
21
22
23
24
25
30
34
36
37
38
39
40

(17 row(s) affected)

@@identity

To find out what is the current identity value for a table:

DBCC CHECKIDENT ('sales02', NORESEED);
go

Checking identity information: current identity value '57', current column value '57'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Note that is is the current number, not the next value that will be used.

By the way:

SELECT SCOPE_IDENTITY() AS 'Last identity used';
go
 
LAST IDENTITY used
---------------------------------------
NULL
 
(1 ROW(s) affected)
 
SELECT @@IDENTITY  AS 'Last identity used';
go
 
LAST IDENTITY used
---------------------------------------
NULL
 
(1 ROW(s) affected)

We get a null value, because, there was no INSERT. The new identity only happens on INSERTs.

SQLHacks for Microsoft SQL Server
Home page Administration Dates & Times Data Manipulation FAQs Optimization Summaries Site Map About us About us Copyrights Credits Disclaimers Guaranties Privacy Support policies Feedback	Retrieve What the missing numbers in an identity column with MSSQL What the missing numbers in an identity column with MSSQL Identity columns provide automatic, almost incremental, numbering. You can only have 1 identity column per table. Should be an int, smallint, tinyint, decimal or numeric with scale 0. Can't contain nulls. Can't have any default. The increment is an integer value (1, 10,...) and can not contain decimals. Also, it can't be a 0 increment. Each value is unique. There is no guarantee that it's a consecutive increment, even if the increment is 1. Identity number are NEVER reused, even if somebody delete rows or even the last row. NEVER use identity for invoice numbers, cheque numbers. Data used USE sql911; go SELECT sales_id, sales_date, sales_qty, emp_short FROM sales02; go sales_id sales_date sales_qty emp_short ----------- ----------------------- --------------------------------------- -------------------------------------------------- 1 2005-10-06 10:01:01.000 10.00 000310 2 2006-10-06 10:01:01.000 10.00 000310 3 2005-11-06 10:01:01.000 9.00 000110 4 2006-10-06 10:01:01.000 10.00 000310 5 2005-12-06 11:01:01.000 8.00 000010 6 2006-02-06 13:01:01.000 6.00 000190 11 2006-05-06 16:01:01.000 3.00 000010 12 2004-10-06 10:01:01.000 10.00 000310 13 2005-11-06 10:01:01.000 9.00 000010 14 2006-06-06 17:01:01.000 2.00 000190 15 2006-01-06 12:01:01.000 7.00 000310 16 2006-02-06 13:01:01.000 6.00 000110 17 2005-03-06 14:01:01.000 5.00 000190 18 2006-10-06 10:01:01.000 10.00 000110 19 2006-10-06 10:01:01.000 3.00 000110 26 2006-10-06 10:01:01.000 10.00 000030 27 2004-03-06 14:01:01.000 5.00 000110 28 2005-04-06 15:01:01.000 4.00 000010 29 2005-05-06 16:01:01.000 3.00 000190 31 2006-07-06 19:01:01.000 1.00 000310 32 2006-03-06 14:01:01.000 5.00 000310 33 2006-04-06 15:01:01.000 4.00 000010 35 2006-06-06 17:01:01.000 2.00 000110 41 2007-05-07 11:30:00.000 2.00 000030 42 2007-05-08 13:30:00.000 1.00 000030 43 2006-07-06 19:01:01.000 1.00 000010 44 2007-05-09 14:00:00.000 1.25 000110 45 2007-11-06 10:01:01.000 9.00 000030 46 2007-05-10 14:00:00.000 5.00 000190 47 2007-05-11 14:15:00.000 4.00 000190 48 2007-05-12 15:00:00.000 2.00 000190 49 2007-05-13 16:00:00.000 1.00 000190 (32 row(s) affected) Missing identity numbers in MSSQL There are many ways of getting a list of the missing numbers in MSSQL. My favorite is a number table. I personally use the number table for numbers but also for wording the cheque amounts. A simple self link will only give the first number that is missing, even if the gap is more than 2 numbers. SELECT nums FROM num WHERE num < 10; go num ----------- 1 2 3 4 5 6 7 8 9 (9 row(s) affected) SELECT num AS 'Missing sales_id' FROM nums WHERE num not in ( SELECT sales_id FROM sales02) and num <= (SELECT MAX(sales_id) FROM sales02); go Missing sales_id ---------------- 7 8 9 10 20 21 22 23 24 25 30 34 36 37 38 39 40 (17 row(s) affected) @@identity To find out what is the current identity value for a table: DBCC CHECKIDENT ('sales02', NORESEED); go Checking identity information: current identity value '57', current column value '57'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. Note that is is the current number, not the next value that will be used. By the way: SELECT SCOPE_IDENTITY() AS 'Last identity used'; go LAST IDENTITY used --------------------------------------- NULL (1 ROW(s) affected) SELECT @@IDENTITY AS 'Last identity used'; go LAST IDENTITY used --------------------------------------- NULL (1 ROW(s) affected) We get a null value, because, there was no INSERT. The new identity only happens on INSERTs.
All contents in SQLHacks.com are the property of db Networks Ltd and it's affiliates. All other trademarks not owned by db Networks Ltd or its affiliates that appear on the SQLHacks.com site are the property of their respective owners, especially: Windows^�, MS SQL^�, SQL Server^� which are the property of Microsoft Corp. DB2^� which is the property of IBM Systems. Oracle database^� which is the property of Oracle Corp.