Generating a range quickly using common table expressions

I’m starting to really love the CTE in SQL2005. I was implementing some Python analytics code for a client today which iterated over a timeseries to generate covariance data, and instead of coding a clumsy, buggy WHILE loop, I wrote this simple little CTE and hooked it into a UDF to give me the data I needed. Took me all of thirty seconds. Someone might find it useful.

;WITH cte_range(n) AS(
SELECT 1 UNION ALL
SELECT n + 1 FROM cte_range)
SELECT n FROM cte_range
OPTION( MAXRECURSION 500 )
Advertisements

About this entry