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 )