SQL query to find Missing sequence numbers

Question

How about something like:

  select (select isnull(max(val)+1,1) from mydata where val < md.val) as [from],
     md.val - 1 as [to]
  from mydata md
  where md.val != 1 and not exists (
        select 1 from mydata md2 where md2.val = md.val - 1)

giving summarised results:

from        to
----------- -----------
6           6
8           8
11          14

I know this is a very old post but I wanted to add this solution that I found HERE so that I can find it easier:

WITH Missing (missnum, maxid)
AS
(
 SELECT 1 AS missnum, (select max(id) from @TT)
 UNION ALL
 SELECT missnum + 1, maxid FROM Missing
 WHERE missnum < maxid
)
SELECT missnum
FROM Missing
LEFT OUTER JOIN @TT tt on tt.id = Missing.missnum
WHERE tt.id is NULL
OPTION (MAXRECURSION 0); 

Try with this:

declare @min int
declare @max int

select @min = min(seq_field), @max = max(seq_field) from [Table]

create table #tmp (Field_No int)
while @min <= @max
begin
   if not exists (select * from [Table] where seq_field = @min)
      insert into #tmp (Field_No) values (@min)
   set @min = @min + 1
end
select * from #tmp
drop table #tmp

The best solutions are those that use a temporary table with the sequence. Assuming you build such a table, LEFT JOIN with NULL check should do the job:

SELECT      #sequence.value
FROM        #sequence
LEFT JOIN   MyTable ON #sequence.value = MyTable.value
WHERE       MyTable.value IS NULL

But if you have to repeat this operation often (and more then for 1 sequence in the database), I would create a "static-data" table and have a script to populate it to the MAX(value) of all the tables you need.

SELECT CASE WHEN MAX(column_name) = COUNT(*)
THEN CAST(NULL AS INTEGER)
-- THEN MAX(column_name) + 1 as other option
WHEN MIN(column_name) > 1
THEN 1
WHEN MAX(column_name) <> COUNT(*)
THEN (SELECT MIN(column_name)+1
FROM table_name
WHERE (column_name+ 1)
NOT IN (SELECT column_name FROM table_name))
ELSE NULL END
FROM table_name;

Here is a script to create a stored procedure that returns missing sequential numbers for a given date range.

CREATE PROCEDURE dbo.ddc_RolledBackOrders 
-- Add the parameters for the stored procedure here
@StartDate DATETIME ,
@EndDate DATETIME
AS 
    BEGIN

    SET NOCOUNT ON;

    DECLARE @Min BIGINT
    DECLARE @Max BIGINT
    DECLARE @i BIGINT

    IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL 
        BEGIN
            DROP TABLE #TempTable
        END

    CREATE TABLE #TempTable
        (
          TempOrderNumber BIGINT
        )

    SELECT  @Min = ( SELECT MIN(ordernumber)
                     FROM   dbo.Orders WITH ( NOLOCK )
                     WHERE OrderDate BETWEEN @StartDate AND @EndDate)
    SELECT  @Max = ( SELECT MAX(ordernumber)
                     FROM   dbo.Orders WITH ( NOLOCK )
                     WHERE OrderDate BETWEEN @StartDate AND @EndDate)
    SELECT  @i = @Min

    WHILE @i <= @Max 
        BEGIN
            INSERT  INTO #TempTable
                    SELECT  @i

            SELECT  @i = @i + 1

        END

    SELECT  TempOrderNumber
    FROM    #TempTable
            LEFT JOIN dbo.orders o WITH ( NOLOCK ) ON tempordernumber = o.OrderNumber
    WHERE   o.OrderNumber IS NULL

END

GO

Aren't all given solutions way too complex? wouldn't this be much simpler:

SELECT  *
FROM    (SELECT  row_number() over(order by number) as N from master..spt_values) t
where   N not in (select 1 as sequence union  
        select 2 union 
        select 3 union 
        select 4 union 
        select 5 union 
        select 7 union 
        select 10 union 
        select 15
        )