Simple Recursive CTE to create a tally table

A tally table is a table that contains a series of sequential numbers used as a base table to create other data sets such as calendars. There are many ways to create a tally table but I prefer the way below as its very simple to read and follow. There are many opinions about the best way to populate a tally table, however, in my opinion it doesnt matter as this query should only be run once.

Simple recursive CTE

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

;WITH Numbers AS

(

SELECTn=1--Anchor

UNION ALL

SELECTn+1--Recursive

FROM Numbers

WHEREn+1<=11000

)

SELECT *

INTO dbo.t_TallyTable

FROM Numbers

OPTION(MAXRECURSION1000);

The above code will insert the numbers 1-11000 into a permanent table called t_TallyTable that you can use as the base of other queries.

By default MAXRECURSION is set to 100 and if we ran our query without ‘OPTION (MAXRECURSION 1000)’ then we would be presented with the following error:

Msg 530, Level 16, State 1, Line 1

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

The MAXRECURSION option can be set to 0, however, it is not advised to do this as it can allow a recursive CTE to carry on infinitely if the CTE is badly written.