June 26, 2009

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


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

8 Responses to "Find the Missing Identity Numbers in SQL Server 2005/2008"
  1. Bijayani said...
    January 18, 2010 at 10:54 PM

    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.

  2. Suprotim Agarwal said...
    January 19, 2010 at 1:06 AM

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

  3. Admin said...
    January 19, 2010 at 1:35 AM

    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!

  4. Anonymous said...
    January 23, 2010 at 2:15 PM

    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

  5. Suprotim Agarwal said...
    May 4, 2010 at 10:07 PM

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

  6. Jonathan Issack said...
    May 4, 2010 at 10:42 PM

    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!

  7. Dave Copeland said...
    May 5, 2010 at 7:43 PM

    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.

  8. Sven Huygens said...
    July 6, 2011 at 1:53 AM

    Hi,

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

    Greets

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions