Using common table expressions (CTE) to generate sequences

Hello frnds,
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 10.

Example:-

   1:  Declare @start int, @end int

   2:  Select @start=1, @end=10

   3:   

   4:   

   5:  ;WITH CTESequence(number)

   6:  AS

   7:  (

   8:  SELECT @start

   9:  UNION ALL

  10:  SELECT number +1 FROM CTESequence

  11:  WHERE number<@end

  12:  )

  13:  SELECT * FROM CTESequence

Output is :-