highest unused ID

  • Is there a way to find the highest unused primarykey?

  • Can you be more specific? Primary keys don't have to be sequential, so the concept of 'highest unused' isn't something that applies to all primary keys.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • yea, sorry I guess I meant lowest unused.

    if 1,2,4,5 are used, then 3 would be a result.

  • If it's an identity column, you can use the ident_current() function. Be careful with that though, since if it's run while an insert is being done, it will give you the prior value (before the insert), and the reverse can happen with deletes.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • sounds good to me, thanks.

  • foxjazz (1/8/2009)


    yea, sorry I guess I meant lowest unused.

    if 1,2,4,5 are used, then 3 would be a result.

    So you want the lowest unused value from an identity column? Not a primary key in general?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • the plan would be to use it as the key in an insert statement.

  • Either of the following will meet your requirements... 😉

    SELECT MIN(RowNum)+1

    FROM dbo.JbmTest

    WHERE RowNum+1 NOT IN (SELECT RowNum FROM dbo.JbmTest)

    SELECT MIN(RowNum)+1

    FROM dbo.JbmTest t1

    WHERE NOT EXISTS (SELECT 1 FROM dbo.JbmTest t2 WHERE t1.RowNum+1 = t2.RowNum)

    But, as many will tell you and for many reasons, it's definitely not a best practice to try to recover skipped numbers in an Identity or other sequence column.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • There's a whole section on identifying gaps in Itzik Ben-Gan's book T-SQL Querying. Here's a way to list all the gaps, their start points and stop points:

    SELECT cur+1 AS start_range, nxt-1 AS end_range

    FROM (SELECT col1 AS cur,

    (SELECT MIN(Col1) FROM dbo.T1 AS B

    WHERE b.col1 > A.col1) AS nxt

    FROM dbo.T1 as A) AS D

    WHERE nxt - cur > 1;

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Now, now, Grant... that's outside of the requirements. 😉 You don't want to actually do anything more than the requirements state, especially for this OP, or you end up getting a blast of hooie like I did...

    http://www.sqlservercentral.com/Forums/Topic630096-338-1.aspx#bm630699

    Stick to the exact requirements, eh? And, whatever you do, don't ask any "stupid" questions. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I agree wholeheartedly Jeff. I mean, it's not like we're here to help educate people or want them to actually learn anything. I for one prefer to answer questions with one or two word answers that assume that the question, which usually provides little to no information about their actual system or goals are an accurate and complete description of their problem.

    The goal should always be to give them the quickest and easiest way to do what they'll realize in 2 or 3 years was a really bad idea in the first place and be stuck with, not try to stop them from doing it!

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • foxjazz (1/8/2009)


    the plan would be to use it as the key in an insert statement.

    You're going to be better off letting SQL handle the identity value of new inserts, and using Scope_Identity() to get that value (for other tables, etc.). Will perform better and all that.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Going strictly by the stated requirements, then -2147483648 is the lowest unused value.

  • Jeff Moden (1/9/2009)


    Now, now, Grant... that's outside of the requirements. 😉 You don't want to actually do anything more than the requirements state, especially for this OP, or you end up getting a blast of hooie like I did...

    http://www.sqlservercentral.com/Forums/Topic630096-338-1.aspx#bm630699

    Stick to the exact requirements, eh? And, whatever you do, don't ask any "stupid" questions. 😀

    Apologies. What the hell was I thinking? 😛

    Dude, can I have a porkchop?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • I realize you guys aren't mind readers, maybe I should restate the requirements to say lowest possible positive unused value.

    I found that I already had an identity set, so I didn't use the identity_insert feature and just let sql server do what it does best. Count.

Viewing 15 posts - 1 through 15 (of 41 total)

You must be logged in to reply to this topic. Login to reply