Generating Integer Sequence With SQL Query

Let’s imagine the situation where you had assigned numbers to accounting documents via identity mechanism and some numbers are skipped because these numbers were not committed. Now you want to find out which numbers are missing. This can be made by comparing numbers in database with an integer sequence (1, 2, 3 and so on) in a SQL query. So you need to generate this sequence by means of SQL Server.

There are many ways to do it, but I write a memo about two of them.
1. Source: Pro SQL Server Internals by Dmitri Korotkevich

He uses a series of CTE that generate 2^n integer values. I’ve modified his script a little to a more understandable form. You can generate from 2^17 (131 072) values and up to 2^32 (4 294 967 296) by placing N1…N5 in the N6 CTE.

2. Cross join rows from sys.all_objects
Source: Generate a set or sequence without loops – part 1 by Aaron Bertrand

This method could generate a few millions of rows (4, 5 or 7+ millions) because it depends on the total number of rows in sys.all_objects.

If you need to generate a smaller number of rows, just use SELECT TOP n statement to get n rows precisely.

Leave a Reply

Your email address will not be published. Required fields are marked *