Simple Recursive CTE to create a tally table

On 08 May, 2015

T-SQL

By : Dave Jenkins

No Comments

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.

; WITH Numbers AS
(
	SELECT n = 1 -- Anchor

	UNION ALL

	SELECT	n + 1 -- Recursive
	FROM	Numbers
	WHERE	n + 1 <= 11000
)

SELECT *
INTO dbo.t_TallyTable
FROM Numbers 
OPTION (MAXRECURSION 1000);

; WITH Numbers AS

(

SELECT n = 1 -- Anchor

UNION ALL

SELECT n + 1 -- Recursive

FROM Numbers

WHERE n + 1 <= 11000

)

SELECT *

INTO dbo.t_TallyTable

FROM Numbers

OPTION (MAXRECURSION 1000);

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.

About The Author

Dave Jenkins

Hello! I'm Dave Jenkins and I have been working with MS SQL Server for two years. I'm an MCP (70-461) working towards an MCSA. I love working with MS SQL Server and the BI Stack.

Number of Posts : 26

All Posts by : Dave Jenkins