SQL - Find gaps in custom ID

Transact-SQL
1

Hi Guys!

I would like to ask for your opinions which would be the fastest way to find the first available gap. The situation is the following:
There is a column char(8). The last 2 chars can contain 0,1,...A, B....,Z.
So if we see the last 2 chars then they look like something like this:
00
01
02
...
0A
0B
...
10
11
12
etc...

Should have to find the first gap, for instance in case of:
A1
A2
B0
The first available gap would be A9

In case of:
01
02
03
05
The first available gap would be 04

Let's suppose that the records are ordered by the last 2 chars as a starting point. I am thinking about which way would be the fastest way to find the first gap

Thanks!

2

This belongs to the class of problems known as islands and gaps. See here for a discussion of various methods. If you are on SQL 2012 or later, lag or lead functions probably would get you the fastest result.Otherwise, a combination of row_number function and grouping would be the way to go.

3

Thanks!

4

Instead, just encode the alphanumeric ending as a sequential number, then you can do a very simple numeric check to find missing value(s).

0=0,1=1,...,10='0A',11='0B',...

You can still store the corresponding alphanumeric chars, of course, but use the encoded value to check for gaps, etc..