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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
WITH N1(C) AS ( -- 2^1 = 2 rows SELECT 0 UNION ALL SELECT 0 ), N2(C) AS ( -- 2^2 = 4 rows SELECT 0 FROM N1 AS T1 CROSS JOIN N1 AS T2 ), N3(C) AS ( -- 2^4 = 16 rows SELECT 0 FROM N2 AS T1 CROSS JOIN N2 AS T2 ), N4(C) AS ( -- 2^8 = 256 rows SELECT 0 FROM N3 AS T1 CROSS JOIN N3 AS T2 ), N5(C) AS ( -- 2^16 = 65 536 rows SELECT 0 FROM N4 AS T1 CROSS JOIN N4 AS T2 ), N6(C) AS ( -- 2^4 * 2^16 = 2^20 = 1 048 576 rows SELECT 0 FROM N3 AS T1 CROSS JOIN N5 AS T2 ), IDs(ID) AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM N6 ) SELECT ID FROM IDs; |
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.
|
1 2 3 4 |
SELECT CAST(ROW_NUMBER() OVER (ORDER BY s1.[object_id]) AS int) AS Number FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2 OPTION (MAXDOP 1); |
If you need to generate a smaller number of rows, just use SELECT TOP n statement to get n rows precisely.