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

    azure book business intelligence ctp database dates denali dmv functions gotcha high availability how to howto indexing mirroring performance sql sql friday sql server sql server 2000 sql server 2005 sql server 2008 sql server 2008 r2 sql server 2012 sql server denali sql server integration services 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
    Instapaper

    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 SitingsTwitterFacebookLinkedInHomePageFlickrLTD RSS Feed
    1230 views
    Read More Posts by SQLDenis
    Instapaper

    2 comments

    Comment from: Luciano Evaristo Guerche (Gorše) [Visitor] Email · http://www.google.com/profiles/guerchele
    Luciano Evaristo Guerche (Gorše) 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
    SQLDenis 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