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

Read 2,526 times
(7 in last 30 days)

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