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://darkslategrey-bat-805937.hostingersite.com)
