Dynamic Numbers Table

Using a numbers table is helpful for many things. Like finding gaps in a supposed sequence of primary keys, or generating date ranges or any numerical range. In some cases, you will be in a production system that does not already contain a numbers table and you will also be unable to add one. In this situation, the dynamic numbers table comes in handy.

[cc lang=”sql”]
DECLARE @MaxNumber int = 5000

;WITH Numbers AS
(
SELECT 1 AS Num
UNION ALL
SELECT Num+1
FROM Numbers
WHERE Num <= @MaxNumber ) SELECT * FROM Numbers OPTION(MAXRECURSION 32767) [/cc] There are a couple drawbacks compared to the static numbers table.

1. speed – to generate all the numbers takes about 500ms
2. limit of 32767 numbers