Recursive Common Table Expressions - SQL

One of the best use of the recursive common table expressions in SQL is to generate consecutive numbers within a range from very simple SQL query.

The query below will generate numbers 1 to 100 from a Select statement using recursive common table expression.

;WITH CTE AS

(

SELECT 1 Number

UNION ALL

SELECT Number + 1

FROM CTE

WHERE Number

)

SELECT Number

FROM CTE