Crash gaps in a SQL string

Question

I have a select today that returns me the following result:

I make another select that returns a value, 5 for example. I would need to know which numbers are not between the number 1 to the number 5. In my case I would need the following result 2, 4, 5.

I would need help developing this logic.

Looks like you're trying to rely on `IDENTITY` to provide a sequential numbers. It doesn't. Only something like `ROW_NUMBER()` during a `SELECT` can achieve this. — Thom A, Dec 20 '18 at 16:08
Sample data and expected output would help clarify this a lot. Otherwise you could use a tally table and the minus operator — S3S, Dec 20 '18 at 16:10
[Remarks](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property#remarks): *"**Consecutive values after server restart or other failures** - SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert. If gaps are not acceptable then the application should use its own mechanism to generate key values."* — Thom A, Dec 20 '18 at 16:10

score 0 · Answer 1 · answered Dec 20 '18 at 16:15

Build a cte with the values you are looking for. Then get the values that dont exist in your table

with cte (rn) as (select    1 as rn
                  union all
                  select    rn + 1
                  from      cte
                  where     rn + 1 <= 5)
select  cte.rn
from    cte
where   not exists (select  1 from  actualtable where   nr_volume = cte.rn)

score 0 · Answer 2 · answered Dec 20 '18 at 16:20

Your question wasn't clear, but it seems you wanted numbers that weren't present in the range returned by two queries, but fell within that range. For that, you can use a tally table and a few methods to limit the results. Note, you included 5 in your expected results, but this contradicts your question since you didn't include 1 int he expected results.

declare @table1 table (i int)
insert into @table1
values (1),(3)

declare @table2 table (i int)
insert into @table2
values (5)

declare @max int = (select top 1 i from (select max(i) i from @table1  union select max(i) from @table2) x order by i desc)
declare @min int = (select top 1 i from (select min(i) i from @table1  union select min(i) from @table2) x order by i asc)

;WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )
select N from cteTally
where 
    N not in (select * from @table1) 
    and N not in (select * from @table2)
    and N > @min
    and N < @max
GO

score 0 · Accepted Answer · answered Dec 20 '18 at 16:26

I have created a sample using NOT EXISTS(), I believe it can help you :

--DROP TABLE #Temp
--GO
CREATE TABLE #Temp
(
    NR_VOLUME   INT
)
INSERT INTO #Temp
VALUES (1),(3)

--Create Temp table for number List
--DROP TABLE #NumberList
--GO
CREATE TABLE #NumberList
(
    Seq INT
)

DECLARE @NumberCheckFrom    INT=1
DECLARE @NumberCheckTo  INT=100

WHILE @NumberCheckTo>=@NumberCheckFrom
BEGIN
    INSERT INTO #NumberList
    SELECT @NumberCheckFrom
    SET @NumberCheckFrom +=1
END

DECLARE @NumberFrom INT=0
DECLARE @NumberTo   INT=5

SELECT * 
FROM  #NumberList NL
WHERE Seq>=@NumberFrom 
AND Seq<=@NumberTo
AND NOT EXISTS (SELECT 1 
                FROM #Temp T
                WHERE NL.Seq = T.NR_VOLUME
                )

a while loop... that will not perform well at scale (just a word of advice). Where did the value of @NumberCheckTo come from? Just wondering how you'd handle this dynamically. — S3S, Dec 20 '18 at 16:39
I agree with you. I just put the numbers to find not exists. Normally I would create a table and put the sequences to the table and execute the while for one time only. — Zeki Gumus, Dec 20 '18 at 16:50

Crash gaps in a SQL string

3 Answers3