SQL Server 2008

Question

I have a table like so

ID          NAME
----------- -----------
1           JON
2           JIM
3           BOB

(3 row(s) affected)

What I need its code to select a number that does not exit in the column ID and out put it to a file so in this instance it will be "4".

What i need it to do is start at 1 then check 2,3,and so on until if finds a a number that does not exists in the table.

This code will have to be in SQL Server 2008

You want (highest+1) or first gap? – gbn Dec 09 '10 at 20:28 — gbn, Dec 09 '10 at 20:28
And you don't want to use and Identity column because... – Conrad Frix Dec 09 '10 at 20:28 — Conrad Frix, Dec 09 '10 at 20:28

Thomas · Accepted Answer · 2010-12-09T21:04:06.327

5

What you need is a numbers table or list:

Declare @MaxValue int;
Set @MaxValue = 100;

With Numbers As
    (
    Select 1 As Value
    Union All
    Select Value + 1
    From Numbers
    Where Value <= @MaxValue
    )
Select Min(N.Value)
From Numbers As N
    Left Join MyTable As T
        On T.Id = N.Value
Where T.Id Is Null
OPTION (MAXRECURSION 0)

edited Dec 09 '10 at 21:04

answered Dec 09 '10 at 20:29

Thomas

63,911
12
95
141

1

+1, but you might need to add an `OPTION (MAXRECURSION 0)` to this query. To prevent a CTE infinite loop, you can limit the number of recursion levels allowed for a particular statement by using the `MAXRECURSION` hint and a value between 0 and 32,767 in the OPTION clause of the INSERT, UPDATE, DELETE, or SELECT statement. This lets you control the execution of the statement until you resolve the code problem that is creating the loop. The server-wide default is 100. When 0 is specified, no limit is applied. Only one MAXRECURSION value can be specified per statement. – KM. Dec 09 '10 at 20:35
@KM - Overlooked that. Have added to the answer. – Thomas Dec 09 '10 at 21:04

score 1 · Answer 2 · edited May 23 '17 at 12:26

Can you specify why you need this? It sounds like there may be a better way to satisfy the overall need.

However, if all you need is the next number in the sequence, then this should work:

SELECT MAX(ID) + 1 FROM Table

Edit: I just noticed from Thomas' answer (and re-inspecting the question) that it looks like you're looking for the first gap, which may or may not be the next number. But I guess the overall point still remains... why?

Edit: I'm glad you accepted an answer, but I still think there's more to this. For example, if you just want to be able to "reserve" an ID then there are a couple ways to accomplish this.

GUIDs are good for application-generated IDs, but shouldn't be used as primary keys for performance reasons. You can have a second column as a GUID and use that within your application, allowing a simple auto-increment column to be the primary key. There are further performance considerations to be made, and you should research it.

Conversely, there's something called the Hi/Lo Algorithm for reserving ranges of database IDs. It uses integers, which are great for indexing and make great primary keys. It leaves gaps in the sequence, but that's to be expected anyway even with a regular auto-generated column (such as when a record is deleted).

If there is a requirement that there shouldn't be gaps in the identifiers, that sounds like an odd business requirement and should be analyzed for its true needs. Something like that shouldn't spill over into the primary key in your data persistence.

Note if you use this for INSERT you're creating a race condition — Conrad Frix, Dec 09 '10 at 20:48
@Conrad Frix: Absolutely. I mean, it satisfies the basic need of "give me the next number" but it doesn't really solve what's more likely a deeper issue/requirement. More information is needed for that, as I'm sure there's a better method to be applied that doesn't involve manipulation of the primary identifier field. — David, Dec 09 '10 at 20:52

SQL Server 2008

2 Answers2