T-SQL: Using common table expressions (CTE) to generate sequences

One of the best enhancements in T-SQL with SQL Server 2005 was Common Table Expressions(CTEs). CTEs are very helpful in writing more readable and manageable queries. The good things don’t end here; self-referencing CTEs are a very powerful method of implementing recursion in SQL queries. In this post, I will present a few examples of generating sequences using CTEs.
The following statements create a number sequence from 1 to 1000.

--define start and end limits
Declare @start int, @end int
Select @start=1, @end=1000

;With NumberSequence( Number ) as
(
    Select @start as Number
        union all
    Select Number + 1
        from NumberSequence
        where Number < @end
)

--select result
Select * From NumberSequence Option (MaxRecursion 1000)

And the output is:

Notice that when selecting records from the CTE, we are setting a value for MaxRecursion Option. This one is important since the maximum recursion level for a CTE is set to 100 by default, and so any sequence with more than 100 rows will generate an error. We can turn the recursion limit off by using MaxRecursion 0, but this is not a good idea since it opens the possibility of infinite recursion in case of a small error in CTE.

Here’s another example that creates date sequence from 2009-03-01 to 2009-04-10.

--define start and end limits
Declare @todate datetime, @fromdate datetime
Select @fromdate='2009-03-01', @todate='2009-04-10'

;With DateSequence( Date ) as
(
    Select @fromdate as Date
        union all
    Select dateadd(day, 1, Date)
    	from DateSequence
    	where Date < @todate
)

--select result
Select * from DateSequence option (MaxRecursion 1000)

This one generates an output similar to:

Notice, that this example, that generates date sequences can also be used to create Time Dimension for a Data Warehouse. If the granularity of time dimension is not too fine, CTEs give a nice alternative to loops. Here’s an example:

--define limits
Declare @todate datetime, @fromdate datetime
set @fromdate = '2009-01-01'
set @todate = '2009-12-31'

;With DateSequence( [Date] ) as
(
	Select @fromdate as [Date]
		union all
	Select dateadd(day, 1, [Date])
		from DateSequence
		where Date < @todate
)

--select result
Select
	CONVERT(VARCHAR,[Date],112) as ID,
	[Date] as [Date],
	DATEPART(DAY,[Date]) as [Day],
	CASE
		 WHEN DATEPART(DAY,[Date]) = 1 THEN CAST(DATEPART(DAY,[Date]) AS VARCHAR) + 'st'
		 WHEN DATEPART(DAY,[Date]) = 2 THEN CAST(DATEPART(DAY,[Date]) AS VARCHAR) + 'nd'
		 WHEN DATEPART(DAY,[Date]) = 3 THEN CAST(DATEPART(DAY,[Date]) AS VARCHAR) + 'rd'
		 ELSE CAST(DATEPART(DAY,[Date]) AS VARCHAR) + 'th'
	END as [DaySuffix],
	DATENAME(dw, [Date]) as [DayOfWeek],
	DATEPART(DAYOFYEAR,[Date]) as [DayOfYear],
	DATEPART(WEEK,[Date]) as [WeekOfYear],
	DATEPART(WEEK,[Date]) + 1 - DATEPART(WEEK,CAST(DATEPART(MONTH,[Date]) AS VARCHAR) + '/1/' + CAST(DATEPART(YEAR,[Date]) AS VARCHAR)) as [WeekOfMonth],
	DATEPART(MONTH,[Date]) as [Month],
	DATENAME(MONTH,[Date]) as [MonthName],
	DATEPART(QUARTER,[Date]) as [Quarter],
	CASE DATEPART(QUARTER,[Date])
		WHEN 1 THEN 'First'
		WHEN 2 THEN 'Second'
		WHEN 3 THEN 'Third'
		WHEN 4 THEN 'Fourth'
	END as [QuarterName],
	DATEPART(YEAR,[Date]) as [Year]
from DateSequence option (MaxRecursion 10000)

And here’s the beautiful output of the above query to be used as a time dimension (click to enlarge the image):