Creating Tally Tables using CTE in SQL Server 2005/2008

A Tally table is simply a table with a single column of very well indexed sequential values starting at any value of your requirement and going up to some predefined threshold. The starting value & the ending value should not be arbitrary. It should be as per the requirement in question.

There are n numbers of ways in which a Tally Table could be generated and the most obvious one is by making use of loops.

Here, I would demonstrate how we could generate a Tally Table using CTE. We will make use of the recursive nature of CTEs to get our job done.

Generating a sequence of numbers from 1 to 20

DECLARE @Max AS INT = 20

;WITH CTE AS (

  SELECT 1 Num

  UNION ALL

  SELECT Num + 1 FROM CTE WHERE Num < @Max

)

SELECT * FROM CTE

Generating a sequence of Dates starting with the current date & going till next 20 days

DECLARE @MaxDate AS DATETIME = GETDATE() + 20

;WITH CTE AS (

  SELECT GETDATE() Dates

  UNION ALL

  SELECT Dates + 1 FROM CTE WHERE Dates < @MaxDate

)

SELECT * FROM CTE

I love this way of generating the Tally Tables as it’s just too simple and on the fly.