Query to Find Missing Number and ID in SQL Server

In this post, I will give you one full demo to find a missing number of identity column in SQL Server.

Before a few days ago, I was working on production report and found that some number is missing. This sequence and missing number are very important.
Immediately one requirement come to my desk is, please find a list of all missing number for investigation purpose.

Below is a full demo script to find the missing number in SQL Server.

First Create one sample table with data.

Below is step by step script to find this missing number.

Result of Step 5:Missing number in sql serverIn the above result you can find total 20 missing number. MissingID Column represents missing numbers.

Anvesh Patel

Leave a Reply

2 Comments on "Query to Find Missing Number and ID in SQL Server"

Notify of
avatar
Sort by:   newest | oldest | most voted
rakhi
Guest

Its good. Bt some what lengthy process it seems.. is there any simple way to get the same?

wpDiscuz