SQL Server : how to detect unregarded values from a range of integers

Question

I have a problem, in example: I have a range of ids of integers (from 1 to 1000), this range supposed to be ids in a SQL Server table, and I want to detect which numbers of this range are not in the table, and sorry for my bad english, thank you

what about this solution in c#:first load table from sql into a c# data table then using a for loop check range one by one and save unregarded id's in an array — user3648435, Feb 15 '15 at 15:08

score 1 · Answer 1 · edited May 23 '17 at 10:33

This sounds like one of the many scenarios where it is helpful to have a numbers table:

SELECT *
FROM lkp_Numbers a
WHERE NOT EXISTS (SELECT 1
                  FROM YourTable b
                  WHERE a.num = b.Id)
  AND num <= 1000

I use this to create a numbers table:

DROP TABLE lkp_Numbers
DECLARE @RunDate datetime 
SET @RunDate=GETDATE()
SELECT TOP 1000 IDENTITY(int,1,1) AS Num
    INTO lkp_Numbers
    FROM sys.objects s1, sys.objects s2, sys.objects s3
ALTER TABLE lkp_Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Num)

That method for creating a numbers table was found here: What is the best way to create and populate a numbers table?

score 1 · Answer 2 · answered Feb 15 '15 at 15:17

One way to find "holes" is to generate a list of all possible values and then look for the ones that aren't there. If you can survive with a list of a missing value and then the number of subsequent values following it, you can do this with another method.

SQL Server 2012+ supports lead() and lag(). The following gets almost everything, except for initial missing values:

select t.id + 1 as missingid,
       (coalesce(t.nextid, 1000) - t.id - 1) as nummissing
from (select t.*, lead(t.id) over (order by t.id) as nextid
      from table t
      t.id between 1 and 1000 
     ) t
where t.nextid > t.id + 1 or
      (t.nextid is null and t.id <> 1000)

You can get these with a little piece of special logic:

select (case when t.previd is null then 1
             else t.id + 1
        end) as missingid,
       (case when t.previd is null then t.id - 1
             else (coalesce(t.nextid, 1000) - t.id - 1) 
        end) as nummissing
from (select t.*, lead(t.id) over (order by t.id) as nextid,
             lag(t.id) over (order by t.id) as previd
      from table t
      where t.id between 1 and 1000 and
     ) t
where (t.nextid > t.id + 1 or
       (t.nextid is null and t.id <> 1000)
       (t.previd is null and t.id <> 1)
      )

M.Ali · Accepted Answer · 2015-02-15T15:44:50.007

another simpler option would be to use the following query

SELECT number 
FROM master..spt_values
WHERE number BETWEEN 1 AND 1000
 AND NOT EXISTS ( SELECT 1 
                  FROM Your_Table t    --<-- your table where you are checking
                  WHERE t.ID = number)    -- the missing values 
GROUP BY number

The above solution is only good if you are looking for around 1000 values. For more values you would need to modify it little bit, something like

-- Select the maximum number or IDs you want to check 
DECLARE @Max_Num INT = 10000;

;WITH CTE AS 
(
  SELECT TOP (@Max_Num) ROW_NUMBER() OVER ( ORDER BY (SELECT NULL)) numbers 
  FROM master..spt_values v1 cross join master..spt_values v2
) 
SELECT c.numbers 
FROM CTE c
WHERE NOT EXISTS (SELECT 1 
                  FROM Your_table t
                  WHERE t.ID = c.numbers)

SQL Server : how to detect unregarded values from a range of integers

3 Answers3