I have previously written two articles on the subject of missing identity and both are very well received by community. I had great fun to write article as many SQL Server expert participated in both the articles. Expert Imran Mohammed had provided excellent script to find missing identity. Please read both the articles for additional information before reading this article about finding gaps in the sequence.
SQL SERVER – Discussion – Effect of Missing Identity on System – Real World Scenario
SQL SERVER – List All Missing Identity Values of Table in Database
Well what really interesting is that another user asked similar question to find gaps in sequence. Two different expert Brian and Tejas responded with excellent solution. Let us go over questions and their solutions.
Question by Enrico
Hi Pinal/Folks,
Can anyone please help me? I am stunned at this one.
Let’s say I have the following records:
INV0096
INV0097
INV0099
INV0100
INV0102
INV0103
How can I generate a SQL Script that will show me the gaps in the sequence?
In such a way that the results will give me INV0098 and INV0101.
Or even just the number 98, and 101.
Thank you so much for your time.
Best regards,
Enrico
First Solution by Brian Tkatch
/* Brian Tkatch Solution 1 */ WITH Data(Datum) AS ( SELECT 'INV0096' UNION ALL SELECT 'INV0097' UNION ALL SELECT 'INV0099' UNION ALL SELECT 'INV0100' UNION ALL SELECT 'INV0102' UNION ALL SELECT 'INV0103' ), CTE AS ( SELECT CAST(SUBSTRING(MIN(Datum), 4, 4) AS INT) Start, CAST(SUBSTRING(MAX(Datum), 4, 4) AS INT) Finish FROM Data UNION ALL SELECT Start + 1, Finish FROM CTE WHERE Start < Finish ) SELECT Common.Formatted FROM CTE CROSS APPLY(SELECT 'INV' + RIGHT('0000' + CAST(Start AS VARCHAR(4)), 4)) Common(Formatted) WHERE NOT EXISTS ( SELECT * FROM Data WHERE Data.Datum = Common.Formatted ) OPTION (MAXRECURSION 0);
Second Solution by Brian Tkatch
/* Brian Tkatch Solution 2 The CROSS APPLY is just nice, but not required. Without it, the query is very similar: */ WITH Data(Datum) AS ( SELECT 'INV0096' UNION ALL SELECT 'INV0097' UNION ALL SELECT 'INV0099' UNION ALL SELECT 'INV0100' UNION ALL SELECT 'INV0102' UNION ALL SELECT 'INV0103' ), CTE AS ( SELECT CAST(SUBSTRING(MIN(Datum), 4, 4) AS INT) Start, CAST(SUBSTRING(MAX(Datum), 4, 4) AS INT) Finish FROM Data UNION ALL SELECT Start + 1, Finish FROM CTE WHERE Start < Finish ) SELECT 'INV' + RIGHT('0000' + CAST(Start AS VARCHAR(4)), 4) FROM CTE WHERE NOT EXISTS ( SELECT * FROM Data WHERE Data.Datum = 'INV' + RIGHT('0000' + CAST(Start AS VARCHAR(4)), 4) ) OPTION (MAXRECURSION 0);
Third Solution by Tejas Shah
/* Tejas Shah Solution 3 */ DECLARE @Test TABLE (Data VARCHAR(10)) INSERT INTO @test SELECT 'INV0096' UNION ALL SELECT 'INV0097' UNION ALL SELECT 'INV0099' UNION ALL SELECT 'INV0100' UNION ALL SELECT 'INV0102' UNION ALL SELECT 'INV0103' UNION ALL SELECT 'INV0106' UNION ALL SELECT 'INV0110' ;WITH cte1 AS( SELECT CAST(RIGHT(Data,4) AS INT) AS RowID FROM @Test ), Missing AS( SELECT MIN(RowID) AS MissNum, MAX(RowID) AS MaxID FROM Cte1 UNION ALL SELECT MissNum + 1, MaxID FROM Missing WHERE MissNum < MaxID ) SELECT missnum FROM Missing LEFT JOIN cte1 tt ON tt.Rowid = Missing.MissNum WHERE tt.Rowid IS NULL OPTION (MAXRECURSION 0);
Let me know if you know any other trick to achieve the same.
Reference : Pinal Dave (https://blog.sqlauthority.com)
16 Comments. Leave new
DECLARE @Data TABLE (Datum VARCHAR(10));
INSERT INTO @Data
SELECT ‘INV0096’
UNION ALL
SELECT ‘INV0097’
UNION ALL
SELECT ‘INV0099’
UNION ALL
SELECT ‘INV0100’
UNION ALL
SELECT ‘INV0102’
UNION ALL
SELECT ‘INV0103’
UNION ALL
SELECT ‘INV0106’
UNION ALL
SELECT ‘INV0110’;
WITH DataInt(Item)
AS
(
SELECT CAST(SUBSTRING(Datum, 4, 4) AS INT)
FROM @Data
),
Nbrs_3( n ) AS ( SELECT 1 UNION SELECT 0 ),
Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ),
Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),
Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 ),
Integers (n)
AS
(
SELECT D.n
FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n)
FROM Nbrs ) AS D(n)
WHERE D.n >= (SELECT MIN(Item)
FROM DataInt)
AND D.n <= (SELECT MAX(Item)
FROM DataInt)
)
SELECT Integers.*
FROM DataInt
RIGHT JOIN Integers
ON DataInt.Item = Integers.n
WHERE DataInt.Item IS NULL
not the best way but this is also working
DECLARE @Test TABLE (Data VARCHAR(10))
INSERT INTO @test
SELECT ‘INV0096’
UNION ALL
SELECT ‘INV0097’
UNION ALL
SELECT ‘INV0099’
UNION ALL
SELECT ‘INV0100’
UNION ALL
SELECT ‘INV0102’
UNION ALL
SELECT ‘INV0103’
UNION ALL
SELECT ‘INV0106’
UNION ALL
SELECT ‘INV0110′
select number from master..spt_values where
number not in (select right(data,4) from @test) and
number between (select min(right(data,4)) from @test)
and (select max(right(data,4)) from @test) and TYPE=’P’
declare @tbl table (data varchar(10))
insert into @tbl
select ‘inv0096’
union all
select ‘inv0097’
union all
select ‘inv0099’
union all
select ‘inv0100’
union all
select ‘inv0102’
union all
select ‘inv0103’
union all
select ‘inv0106’
union all
select ‘inv0110’
declare @i int
select @i = 0
declare @integer table(i int)
while @i < 1000
begin
insert into @integer(i) select @i
select @i = @i + 1
end
select i
from @integer
left join @tbl on i = cast(right(data, 4) as int)
where data is null
and i = (select min(cast(right(data, 4) as int)) from @tbl)
sorry,
but < and > disappeared
where clause
where data is null
and i <= (select max(cast(right(data, 4) as int)) from @tbl)
and i >= (select min(cast(right(data, 4) as int)) from @tbl)
declare @Org_tab table (string varchar(100))
declare @Comp_tab table (Comp_value int)
declare @min int,@max int,@i int
insert into @Org_tab
select ‘INV0096’
union all
select ‘INV0097’
union all
select ‘INV0099’
union all
select ‘INV0100’
union all
select ‘INV0102’
union all
select ‘INV0103’
set @min=(select min(cast(substring(string,4,4) as int)) from @Org_tab)
set @max=(select max(cast(substring(string,4,4) as int)) from @Org_tab)
while @min <= @max
begin
insert into @Comp_tab(Comp_value) select @min
select @min = @min + 1
end
select distinct Comp_value from @Comp_tab,@Org_tab
where Comp_value not in (select cast(substring(string,4,4) as int) from @Org_tab)
declare
c2 cursor for select data from test
declare
@varStrings varchar(50),
@i int
begin
open c2
fetch c2 into @varStrings
set @i=96
while @@fetch_status=0
begin
if cast(substring(@varStrings,4,4) as int) @i
begin
select ‘inv’+cast(@i as varchar)
set @i=@i+2
end
else
begin
set @i=@i+1
end
fetch c2 into @varStrings
end
close c2
deallocate c2
end
Hi Dave,
DECLARE @test Table(InvOID varchar(10))
INSERT INTO @test
VALUES(‘INV0096’),(‘INV0097’),(‘INV0100’)
DECLARE @MinOID INT
DECLARE @MaxOID INT
SELECT @MinOID = MIN(CONVERT(INT,SUBSTRING(InvOID,4,4))) FROM @test
SELECT @MaxOID = MAX(CONVERT(INT,SUBSTRING(InvOID,4,4))) FROM @test
WHILE(@MinOID <= @MaxOID)
BEGIN
IF NOT EXISTS(SELECT 1 FROM @test WHERE InvOID = 'INV'+ RIGHT('0000'+CONVERT(VARCHAR(4),@MinOID),4))
BEGIN
SELECT 'INV'+CONVERT(VARCHAR(4),@MinOID)
END
SET @MinOID = @MinOID + 1
END
Cheers,
Rajesh S
I thing article machine should slow down a bit, and should not race for SEO. .. Its important to validate your solution.
The great article factory
Try something like this, easy way of getting things done
SET NOCOUNT ON
IF OBJECT_ID(‘tempdb..#tempInvoiceNumbers’) IS NOT NULL
DROP TABLE #tempInvoiceNumbers
CREATE TABLE #tempInvoiceNumbers
(InvoiceNumbers INT)
DECLARE @minInvoiceNumber INT
DECLARE @maxInvoiceNumber INT
DECLARE @index INT
SET @minInvoiceNumber =(SELECT MIN (InvoiceNumber) FROM dbo.BillHeaders)
SET @maxInvoiceNumber =(SELECT MAX(InvoiceNumber) FROM dbo.BillHeaders)
SET @index = @minInvoiceNumber
WHILE @index != @maxInvoiceNumber
BEGIN
INSERT INTO #tempInvoiceNumbers
( InvoiceNumbers )
VALUES ( @index )
SET @index=@index+1
END
SELECT I.InvoiceNumbers, dbo.BillHeaders.InvoiceNumber FROM #tempInvoiceNumbers AS I
LEFT JOIN dbo.BillHeaders ON InvoiceNumbers= dbo.BillHeaders.InvoiceNumber
WHERE dbo.BillHeaders.InvoiceNumber IS NOT NULL
Rajesh S
ROCKS!
I’m using following code to to find out missing sequence in part transactiion ID from a table parttransactions anything above 6000000. To some reason when I execute this code I don’t get any result but I get message says “Query has finished processing” . Any advice what I’m missing?
declare @min int
declare @max int
create table #ZS (tempID int)
select @min = min(imtPartTransactionID), @max = max(imtPartTransactionID) from PartTransactions where imtPartTransactionID > 6000000
while @min <= @max
begin
if not exists (select * from PartTransactions where imtPartTransactionID = @min)
insert into #ZS (tempID) values (@min)
set @min = @min + 1
end
select * from #ZS
drop table #ZS
Rajesh S’ a bit more perfectionist version :P
– briliant simplicy Rajesh! I really liked your idea.
— insert test data
INSERT INTO @test
VALUES(‘INV0096’),(‘INV0097’),(‘INV0100’),(‘INV0101’),(‘INV0103’),(‘INV0104’)
— find min and max number from the range
DECLARE @MinOID INT
DECLARE @MaxOID INT
SELECT @MinOID = MIN(CONVERT(INT,SUBSTRING(InvOID,4,4))) FROM @test
SELECT @MaxOID = MAX(CONVERT(INT,SUBSTRING(InvOID,4,4))) FROM @test
— loop through all table rows
WHILE(@MinOID <= @MaxOID)
BEGIN
IF NOT EXISTS(SELECT 1 FROM @test WHERE InvOID = 'INV'+ RIGHT('0000'+CONVERT(VARCHAR(4),@MinOID),4))
BEGIN
SELECT 'INV' + REPLICATE('0',4-LEN(CONVERT(VARCHAR(4),@MinOID))) + CONVERT(VARCHAR(4),@MinOID)
END
SET @MinOID = @MinOID + 1
END
create table #temp
(
ID int identity(1,1),
Name varchar(10)
)
insert into #temp(Name) Values(‘nikunj’)
insert into #temp(Name) Values(‘sandeep’)
insert into #temp(Name) Values(‘ajay’)
insert into #temp(Name) Values(‘rohit’)
insert into #temp(Name) Values(‘vishal’)
insert into #temp(Name) Values(‘vipin’)
insert into #temp(Name) Values(‘mohit’)
insert into #temp(Name) Values(‘sumit’)
insert into #temp(Name) Values(‘dev’)
insert into #temp(Name) Values(‘vicky’)
–select * from #temp
delete from #temp where ID in (1,4,6,9,10)
–select * from #temp
;with cte as (
select cast(IDENT_SEED(‘#temp’) as int) as ‘RowNo’
union all
select RowNo+cast(IDENT_INCR(‘#temp’) as int) as ‘RowNo’
from cte
where RowNo<cast(IDENT_CURRENT('#temp') as int)
)
select a.RowNo
from cte a
left outer join #temp b on a.RowNo=b.ID
where b.ID is null
drop table #temp
How about the below Query
create table temp
(
id int identity(1,1),
ids int
);
insert into temp values(2)
go 1000
delete from dbo.temp WHERE ID IN(2,5,7,10,34,65,78,90,123,456)
delete from dbo.temp where id in(7,10,34,65,78,90,123,456)
SELECT a.ID+1 FROM TEMP A
LEFT JOIN TEMP B
ON A.ID+1=B.ID
WHERE B.ID IS NULL
Remove the Last number as it will be an extra record.
Hi, The solutions Provided here were nice. But here in example you took all the values have prefix ‘INV’. If suppose one of the values do not have prefix ‘INV’ just the number. So like this
‘INV0096′,’INV0097′,’INV0100′,’0103’.. How about this??
I think using REPLACE instead of SUBSTRING(MIN(Datum), 4, 4) will cover that up