Multiple Ways to Find Missing Serial Numbers in SQL

In my previous blogs, I had mentioned that there will be many ways to solve a problem. The below is one more example. 

Often, in some tables where identity column exists, will have some missing sequences due to some data fixes. Or we may have some sequential numbers or numeric ranges in a table from which we may need to find out the missing number or ranges.

Let's create a temporary table with values before we look into the different methods to accomplish the goal:

/* SOLUTION-1 - Identify only missed numbers   */

/* SOLUTION-2 - Identify the range that has missed values */

/* SOLUTION-3 - Identify the range of missed values */

Credits: Marc Gravell

/* SOLUTION-4 - Identify the missed values                 */

Credits Suprotim Agarwal

 

 If you find any other method, please do share in the comments section.