Find Missing Identity Values In Auto Increment Column-CodePudding

I have table with identity column seqno. I want to find missing no in this column.

Note : I have only read permission in this table only.

My code:

SELECT Rno 
FROM 
    (SELECT 
         seqno, ROW_NUMBER() OVER (ORDER BY seqno) AS Rno 
     FROM trnmast) a 
WHERE 
    seqno <> rno

I run this but not getting correct result.

Example :

SeqNo
1
3
4
7
8
10

I want only missing no like this :

seqNo
2
5
6
9

I have only read permission of this table only.

CodePudding user response：

You can create a lookup table (a table variable) contains the seqno from 1 to the maximum number in your table.

Query

declare @t as table(seqno int);
declare @n as int, @s as int;
set @s = 1;
select @n = max(seqno) from yourTable;
while(@s <= @n)
begin
  insert into @t
  select @s;
  set @s  = 1;
end

select * from @t
where seqno not in (select seqno from yourTable);

CodePudding user response：

The easiest would be to have a "tally" (numbers) table (that contains a column Number with the numbers, from 1 through (n) - however high you want to go) and then do something like this:

SELECT
    number
FROM
    dbo.TallyTable tt
LEFT OUTER JOIN
    dbo.trnmast trn ON tt.Number = trn.seqno
WHERE
    trn.seqno IS NULL

Take the tally table, left join to your trnmast table on the seqno and get those entries from the tally table that aren't present in the trnmast table.

Of course - you must first have (or create) such a tally table - there are lots of blog posts and articles on how to do that - just do some research and you'll find ways to do this easily!