Login or Sign Up to become a member!
LessThanDot Sit Logo

LessThanDot

Data Management

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot friendfeed Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Your profile

Tools

Tag cloud

backup book business intelligence database dates dmv functions gemini gotcha high availability how to howto indexing interview madison mirroring performance performance tuning restore sql sql friday sql server sql server 2000 sql server 2005 sql server 2008 sql server 2008 r2 ssis t-sql tip trick

Authors

Main Categories

Search

XML Feeds

What is RSS?

Google Ads

« Activity Monitor to gather statistics and health of SQL Server 2008A Little XML Can Go a Long Way »
2
comments

How to return all the skipped identity values from a table in SQL Server

by SQLDenis on Jan 18, 2010 in categories Database Programming, Microsoft SQL Server
Rate Post:
submit to reddit Digg!FacebookDotnetkicks

Every now and then someone will ask how to return a list of all the identity values in a table that have been skipped. You will probably have a table with an identity column, the 'problem' with identity columns is that if an insert is rolled back or fails in any way then the identity value is not reused...you end up with gaps. Identifying gaps is pretty easy to do if you have a table of numbers in your database.

If you don't have a table of numbers, here is some code that will create a table with numbers between 1` and 2048

tsql
Line number Off | Hide | Select all
  1. CREATE TABLE Numbers (number INT not null PRIMARY KEY )
  2. go
  3. INSERT Numbers
  4. SELECT number + 1
  5. FROM master..spt_values s
  6. WHERE s.type='P'

Now that we have our numbers table we can proceed with creating another table which we will populate with some numbers

tsql
Line number Off | Hide | Select all
  1. CREATE TABLE #bla(id INT)
  2.  
  3. INSERT #bla VALUES(1)
  4. INSERT #bla VALUES(2)
  5. INSERT #bla VALUES(4)
  6. INSERT #bla VALUES(5)
  7. INSERT #bla VALUES(9)
  8. INSERT #bla VALUES(12)

Here is the code that will return the gaps (the values 3,6,7,8,10,11) from the temp table

tsql
Line number Off | Hide | Select all
  1. SELECT number
  2. FROM Numbers n
  3. LEFT join #bla b ON n.number = b.id
  4. WHERE n.number < (SELECT MAX(id) FROM #bla)
  5. and  b.id IS null

As you can see it is a simple left join, we also check for the max value otherwise you would get everything back that is greater than the max value in the #bla table.



*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum

About the Author

User bio imageDenis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
Social SitingsTwitterFacebookLinkedInHomePageLTD RSS Feed
511 views
More Posts by SQLDenis
gaps, how to, identity, sql server 2005, sql server 2008
submit to reddit Digg!FacebookDotnetkicks

Comments and Feedback

2 comments

Comment from: Luciano Evaristo Guerche (Gorše) [Visitor] Email · http://www.google.com/profiles/guerchele
Denis,

I would appreciate your comments about my implementation using CTEs.

--

CREATE TABLE #bla(id INT PRIMARY KEY CLUSTERED);

INSERT #bla VALUES(1);
INSERT #bla VALUES(2);
INSERT #bla VALUES(4);
INSERT #bla VALUES(5);
INSERT #bla VALUES(9);
INSERT #bla VALUES(12);

WITH NumbersCTE (Number)
AS
(
SELECT CAST(1 AS int) As Number
UNION ALL
SELECT NumbersCTE.Number + 1 AS Number
FROM NumbersCTE
WHERE EXISTS(
SELECT #bla.id
FROM #bla
WHERE #bla.id > NumbersCTE.Number
)
)
SELECT NumbersCTE.Number
FROM NumbersCTE
LEFT OUTER JOIN
#bla
ON NumbersCTE.Number = #bla.id
WHERE #bla.id IS NULL;
01/19/10 @ 12:43
Comment from: SQLDenis [Member] Email
It is a nice solution

I checked the reads between the two solutions

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

set statistics io on

SELECT number
FROM Numbers n
LEFT join #bla b ON n.number = b.id
WHERE n.number < (SELECT MAX(id) FROM #bla)
and b.id IS null
go

WITH NumbersCTE (Number)
AS
(
SELECT CAST(1 AS int) As Number
UNION ALL
SELECT NumbersCTE.Number + 1 AS Number
FROM NumbersCTE
WHERE EXISTS(
SELECT #bla.id
FROM #bla
WHERE #bla.id > NumbersCTE.Number
)
)
SELECT NumbersCTE.Number
FROM NumbersCTE
LEFT OUTER JOIN
#bla
ON NumbersCTE.Number = #bla.id
WHERE #bla.id IS NULL;

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

Numbers table
--------------
(6 row(s) affected)
Table '#bla'. Scan count 2, logical reads 2,
Table 'Numbers'. Scan count 1, logical reads 2,

CTE
------------
(6 row(s) affected)
Table '#bla'. Scan count 2, logical reads 24,
Table 'Worktable'. Scan count 2, logical reads 74,
01/19/10 @ 13:29

Leave a comment


Your email address will not be revealed on this site.

Your URL will be displayed.
(Line breaks become <br />)
(Name, email & website)
(Allow users to contact you through a message form (your email will not be revealed.)

Powered by B2Evolution
Valid XHTML | Valid CSS