MSSQL: Find the next available number in a sequence

 So I have this data table, with a non-key column, and I need to write a SQL Query to find the next unused number in the sequence that is greater than a certain starting number.



It's like this:

A long time ago, the table started somewhere around 1000.  This is not a key column, and some of the older records have been archived out of the table.  So I have a weird number that will serve as my minimum.   BUT! there are also some wacky big numbers in the system and I don't want to do a max(n)+1.



Here's a beakdown:

0-35956	these numbers are there, with lots of missing numbers and gaps due to archiving.
35957-1904121900	a few of these are there, I want to find the next available number in this region.
1904121901 and up	I want to ignore these numbers, and not use them (until all the numbers leading up to here are filled).

My Goal here is to create a SQL Query I can run once to find the next available number (meaning the lowest number above 35957 that does not exist in the column).



Here is what worked.




select min(Item_No)+1 as NextID

from Data_Table DT

where not exists

(select 1 from Data_Table DT2 where DT2.Item_No=DT.Item_No+1)

and Item_No > 35956



...

---

Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.