SQL query to find Missing sequence numbers

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

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.

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
        )

You could also solve using something like a CTE to generate the full sequence:

create table #tmp(sequence int)

insert into #tmp(sequence) values (1)
insert into #tmp(sequence) values (2)
insert into #tmp(sequence) values (3)
insert into #tmp(sequence) values (5)
insert into #tmp(sequence) values (6)
insert into #tmp(sequence) values (8)
insert into #tmp(sequence) values (10)
insert into #tmp(sequence) values (11)
insert into #tmp(sequence) values (14)

    DECLARE @max INT
    SELECT @max = max(sequence) from #tmp;

    with full_sequence
    (
        Sequence
    )
    as
    (
        SELECT 1 Sequence

        UNION ALL

        SELECT Sequence + 1
        FROM full_sequence
        WHERE Sequence < @max
    )

    SELECT
        full_sequence.sequence
    FROM
        full_sequence
    LEFT JOIN
        #tmp
    ON
        full_sequence.sequence = #tmp.sequence
    WHERE
        #tmp.sequence IS NULL

Hmmmm - the formatting is not working on here for some reason? Can anyone see the problem?

i had made a proc so you can send the table name and the key and the result is a list of missing numbers from the given table

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[action_FindMissing_Autoincremnt]
(
@tblname as nvarchar(50),
@tblKey as nvarchar(50)
)
AS
BEGIN
    SET NOCOUNT ON;

declare @qry nvarchar(4000)



set @qry = 'declare @min int ' 
set @qry = @qry + 'declare @max int '

set @qry = @qry +'select @min = min(' + @tblKey + ')'
set @qry = @qry + ', @max = max('+ @tblKey +') '
set @qry = @qry + ' from '+ @tblname 

set @qry = @qry + ' create table #tmp (Field_No int)
while @min <= @max
begin
   if not exists (select * from '+ @tblname +' where '+ @tblKey +' = @min)
      insert into #tmp (Field_No) values (@min)
   set @min = @min + 1
end
select * from #tmp order by Field_No
drop table #tmp '

exec sp_executesql @qry 

END
GO

SELECT TOP 1 (Id + 1)
FROM CustomerNumberGenerator
WHERE (Id + 1) NOT IN ( SELECT Id FROM CustomerNumberGenerator )

Working on a customer number generator for my company. Not the most efficient but definitely most readable

The table has one Id column. The table allows for Ids to be inserted at manually by a user off sequence. The solution solves the case where the user decided to pick a high number