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.
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)
There are a couple drawbacks compared to the static numbers table.
- speed – to generate all the numbers takes about 500ms
- limit of 32767 numbers
Popular search terms:
