---
title: "How to find missing numbers in a sequence in SQL Server?"  
description: "How to find missing numbers in a sequence in SQL Server?"  
author: "Ashutosh Patel"  
published: 2024-12-27  
updated: 2025-01-06  
canonical: https://www.mindstick.com/forum/161084/how-to-find-missing-numbers-in-a-sequence-in-sql-server  
category: "mssql server"  
tags: ["mssql server", "sql server", "sql"]  
reading_time: 1 minute  

---

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

Write an [SQL Query](https://www.mindstick.com/forum/529/rename-table-name-and-column-name-using-sql-query) to find the [missing](https://answers.mindstick.com/qa/52138/international-missing-children-s-day-2019-was-observed-on) number from a [sequence](https://www.mindstick.com/interview/34504/difference-between-identity-vs-sequence) in [SQL Server](https://www.mindstick.com/articles/12999/what-is-table-valued-function-in-sql-server).

## Replies

### Reply by Khushi Singh

To find missing numbers in a sequence in [SQL](https://www.mindstick.com/articles/13115/types-of-keys-in-sql-or-oracle-database) Server:

**Generate a list of all possible numbers** using a Common Table Expression (CTE):

```plaintext
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:

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


---

Original Source: https://www.mindstick.com/forum/161084/how-to-find-missing-numbers-in-a-sequence-in-sql-server

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.