Technical Article

Identify Missing Sequence/Identity Numbers

,

First create the function "dbo.fn_generate_numbers_v2", which will generate the defined sequence of numbers.

By joining this function with our table, we can able to retrieve the list of missing sequence numbers or identity  numbers.

declare @table table (id int)

declare @max int

insert into @table values (1),(2),(3),(4),(5),(7),(9),(10),(15)

select @max = max(id) from @table

select n.RowNum from dbo.fn_generate_numbers_v2 (@max) n

    left outer join @table t1 on n.RowNum = t1.id

where t1.id is null

Regards,

Vignesh Arulmani

CREATE FUNCTION dbo.fn_generate_numbers_v2
(@NumRows INT)
RETURNS @returnTable TABLE (RowNum INT PRIMARY KEY)
AS
BEGIN

DECLARE @idt INT
SET @idt = 0
WHILE (@idt < @NumRows)
BEGIN
	SELECT @idt = @idt + 1
	INSERT INTO @returnTable
		SELECT @idt
END
RETURN
END
GO;


declare @table table (id int)
declare @max int
insert into @table values (1),(2),(3),(4),(5),(7),(9),(10),(15)
select @max = max(id) from @table
select n.RowNum from dbo.fn_generate_numbers_v2 (@max) n
	left outer join @table t1 on n.RowNum = t1.id 
where t1.id is null

Rate

4.2 (5)

You rated this post out of 5. Change rating

Share

Share

Rate

4.2 (5)

You rated this post out of 5. Change rating