January 8, 2009 at 2:56 pm
Is there a way to find the highest unused primarykey?
January 8, 2009 at 3:06 pm
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
January 8, 2009 at 3:08 pm
yea, sorry I guess I meant lowest unused.
if 1,2,4,5 are used, then 3 would be a result.
January 8, 2009 at 3:09 pm
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
January 8, 2009 at 3:16 pm
sounds good to me, thanks.
January 8, 2009 at 3:17 pm
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
January 8, 2009 at 3:19 pm
the plan would be to use it as the key in an insert statement.
January 8, 2009 at 6:51 pm
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
Change is inevitable... Change for the better is not.
January 9, 2009 at 6:32 am
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
January 9, 2009 at 8:10 am
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
Change is inevitable... Change for the better is not.
January 9, 2009 at 8:20 am
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!
January 9, 2009 at 9:13 am
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
January 9, 2009 at 9:24 am
Going strictly by the stated requirements, then -2147483648 is the lowest unused value.
January 9, 2009 at 10:28 am
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
January 9, 2009 at 1:00 pm
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