About This Blog

.net & SQL Samples, programming tips and tricks, performance tips, guidelines, and best practices

Sunday 22 May 2011

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.

1 comment:

  1. Please see the article at the following link for why you should probably never use a recursive CTE for such a thing...
    http://www.sqlservercentral.com/articles/T-SQL/74118/

    --Jeff Moden

    ReplyDelete