Locate the largest gap in a sequence of numerical values

Question

We have a large table with a field MEMBERNO. The values are roughly sequential with a few gaps due to rows being deleted (the next number logic is a simple

MAX(MEMBERNO) + 1

In T-SQL, can I locate the largest gap between 2 values? I know that I coudl do it with a cursor but am interested to see if it can be done in T-SQL.

So if the sequence is 1,2,4,6,8,12,13,14,15,18 I would like to return 12 as it is the number after the largest gap

Answer 1

Use LAG to get the previous memberno and calculate the gap:

select top(1) memberno, memberno - lag(memberno) over (order by memberno) as gap
from members
order by gap desc;

Here is the SQL fiddle: http://sqlfiddle.com/#!6/79bc7/6.

Here is the same with a sub query instead of LAG:

select top(1) memberno, memberno - 
  (select max(memberno) from members x where x.memberno < members.memberno) as gap
from members
order by gap desc;

The SQL fiddle: http://sqlfiddle.com/#!3/79bc7/2.