Find the Missing Identity Numbers in SQL Server 2005/2008

We as Database Developers often come across tables that have identity columns. Many a times, there are missing rows in that table, which have probably been deleted during a database operation. Have you wondered how to find the missing rows? Well here’s a query written by Markwill that helps you find the missing identity numbers in a table. I found this query extremely useful and thought of sharing it with all of you.

Let’s create some sample data first. For demonstration purposes, please assume that this table has an identity column

-- Create Sample Table
DECLARE @TT table
( ID int,
CategoryGroupID int,
CategoryGroupName varchar(10)
)

-- Create Sample Data
INSERT INTO @TT VALUES ( 1, 1, 'AA')
INSERT INTO @TT VALUES ( 2, 1, 'AA');
INSERT INTO @TT VALUES ( 3, 1, 'AA');
INSERT INTO @TT VALUES ( 4, 2, 'BB');
INSERT INTO @TT VALUES ( 5, 2, 'BB');
INSERT INTO @TT VALUES ( 6, 2, 'BB');
INSERT INTO @TT VALUES ( 7, 2, 'BB');
INSERT INTO @TT VALUES ( 8, 3, 'CC');
INSERT INTO @TT VALUES ( 9, 3, 'CC');
INSERT INTO @TT VALUES ( 10, 3, 'CC');
INSERT INTO @TT VALUES ( 11, 3, 'CC');
INSERT INTO @TT VALUES ( 12, 3, 'CC');
INSERT INTO @TT VALUES ( 13, 4, 'DD');

We will now go ahead and delete a few rows from this table using the following query

DELETE @TT WHERE ID IN(3,6,11);

SELECT ID FROM @TT

On selecting the records, we will see the missing identity numbers as shown below:

image

As you can observe from the screenshot above, ID 3, 6 and 11 are missing.

So how do we programmatically determine missing numbers? Here’s the query to detect missing identity numbers

;WITH Missing (missnum, maxid)
AS
(
SELECT 1 AS missnum, (select max(id) from @TT)
UNION ALL
SELECT
missnum + 1, maxid FROM Missing
WHERE missnum < maxid
)
SELECT missnum
FROM Missing
LEFT OUTER JOIN @TT tt on tt.id = Missing.missnum
WHERE tt.id is NULL
OPTION
(MAXRECURSION 0);

OUTPUT

image


About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

12 comments:

Bijayani said...

Hi,

Though Iam not a technical person but find your post quite informative. I would like to share a link where a software engineer of my company has shared a tip on "How to get the Deleted Identity Numbers in SQL Server 2005". Iam sharing this link because I find it very much similar to your blog entry.

Here is the link
http://www.mindfiresolutions.com/How-to-get-the-Deleted-Identity-Numbers-in-SQL-Server-2005-799.php

Hope you find it useful and of assistance.

Thanks,
Bijayani
Mindfire Solutions:India's Only Company to be both Apple Premier & Microsoft Gold certified.

Suprotim Agarwal said...

Yes and that looks like a copyright violation because the code is an exact copy of mine. Please remove the article from your site.

Subhendu said...

Suprotim.. Gimme a break!

What you wrote and what is written on that other link the girl shared above is with the same syntax, because syntax does not change ever.

And I did see that the updated date of that girl's link is 2009 where as you have created this blog post today morning in 2010.

You copied from there. Did you!
Gimme a brk!

Anonymous said...

Put a 4 million row gap in that and see how long it takes. The recursive CTE is a killer in this code.

--Jeff Moden

Suprotim Agarwal said...

Subendhu, this post was created by me on June 26 2009. You are smart enough to judge who copied who!

Jonathan Issack said...

Suprotim, plagiarism is a serious issue and unfortunately, it cannot be curbed completely.

I have been visiting this blog from the past several months, and many like me would know that the content here is original. So why bother? They are probably trying to save their face protecting the employee who copied your post and put in on their site. Just Ignore!

Dave Copeland said...

I second what Jonathan said. it happens to all of us all the time. What such companies do not realize is by ignoring these malpractices by its employees, they are just making them copycats.

Sven Huygens said...

Hi,

instead of using the left outer join it is also possible to use: 'WHERE missnum NOT IN (SELECT id from @TT)'

Greets

Unknown said...

Hi,
Nice help.
I have also created one alternative solution to find missing id from tables.
Please refer this link:
http://www.dbrnd.com/2015/06/query-to-find-missing-number-id-sql-server/

Nilesh D Sonpasare said...

--Some Modified Query
;WITH Missing (missnum, maxid)
AS
(
SELECT 1 AS missnum, (select max(ItemseqNumber) from tblitemsequnceMaster where batchhId=65)
UNION ALL
SELECT missnum + 1, maxid FROM Missing
WHERE missnum < maxid
)
SELECT top 1 missnum
FROM Missing
where missnum not in (select ItemseqNumber from tblitemsequnceMaster where batchhId=65)
--LEFT OUTER JOIN tblitemsequnceMaster tt on tt.ItemseqNumber = Missing.missnum
--WHERE tt.ItemseqNumber is NULL
OPTION (MAXRECURSION 0);

Nilesh D Sonpasare said...

Modified query

;WITH Missing (missnum, maxid)
AS
(
SELECT 1 AS missnum, (select max(ItemseqNumber) from tblitemsequnceMaster where batchhId=65)
UNION ALL
SELECT missnum + 1, maxid FROM Missing
WHERE missnum < maxid
)
SELECT top 1 missnum
FROM Missing
where missnum not in (select ItemseqNumber from tblitemsequnceMaster where batchhId=65)

OPTION (MAXRECURSION 0);

Nilesh D Sonpasare said...

;WITH Missing (missnum, maxid)
AS
(
SELECT 1 AS missnum, (select max(ItemseqNumber) from tblitemsequnceMaster where batchhId=65)
UNION ALL
SELECT missnum + 1, maxid FROM Missing
WHERE missnum < maxid
)
SELECT top 1 missnum
FROM Missing
where missnum not in (select ItemseqNumber from tblitemsequnceMaster where batchhId=65)
--LEFT OUTER JOIN tblitemsequnceMaster tt on tt.ItemseqNumber = Missing.missnum
--WHERE tt.ItemseqNumber is NULL
OPTION (MAXRECURSION 0);