How to find missing numbers in a sequence in SQL Server?

Question

Khushi Singh · Answer

To find missing numbers in a sequence in SQL Server:
Generate a list of all possible numbers using a Common Table Expression (CTE):
WITH AllNumbers AS (
   SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Number
   FROM master.dbo.spt_values
)
This creates numbers from 1 to 100.
Find missing numbers by comparing with your table:
SELECT Number
FROM AllNumbers
WHERE Number NOT IN (SELECT Number FROM NumbersTable);
This shows numbers missing from your sequence. Adjust TOP (100) to match your range.
and 
and 
and 
and 
and 
and 
and 
and 
and 
and 
and

forum

How to find missing numbers in a sequence in SQL Server?

Ashutosh Kumar Verma

Can you answer this question?

1 Answers

Liked By