SQL SERVER – Find Gaps in The Sequence

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.

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.

Download script used in this article

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);

Click to Download Scripts

Let me know if you know any other trick to achieve the same.

Reference : Pinal Dave (http://blog.sqlauthority.com)

About these ads

15 thoughts on “SQL SERVER – Find Gaps in The Sequence

  1. 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

  2. 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’

  3. 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)

  4. 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)

  5. 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)

  6. 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

  7. 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

  8. 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

  9. 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

  10. 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

  11. 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

  12. Pingback: SQL SERVER – Weekly Series – Memory Lane – #045 | Journey to SQL Authority with Pinal Dave

  13. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s