I recently had a need to use recursive Common Table Expression (CTE) query to meet a real business requirement.

Before I get into my real business situation, I want to blog about a couple of simple examples of using recursive CTE I found on beyondrelational.com (Google tsql challenges). I don’t who those people are, and how they collaborate on their work of providing those T-SQL challenges and picking winners for best solutions, but they are doing a fantastic job of promoting SET based query writing skills. According to this article T-SQL Challenge #1 on SQLServerCentral.com, TSQL Challenge Committee is a group of SQL Server experts from around the world. Many of them are SQL Server MVPs, some are SQL Server consultants and others are developers working with SQL Server for a number of years.

Anyway, here is the one simple example from beyondrelational.com of generating a date series using a recursive CTE. I put my comments here to make a couple of points.

1) Using UNION ALL and refer to the CTE makes the query recursive.

2) It’s important to know when to terminate the recursive query. The WHERE clause here serves the purpose of terminating the recursive part by setting a upper limit for the date.

If you are new to writing SQL queries, this might not make much sense to you. You might wonder why cannot you use a cursor or a WHILE loop. Well, the purpose here is to write SET based query rather than record-by-record sequential “program”.

Even a simple example like this has real life usage. You can use this date series to do a Cross Joint (Cartesian Product) with a dimension table, such as Product, to create a product catalog.