Recursive CTE

With T As

( Select    1 N -- first/base action

Union All

Select    N+1

From    T

Where    N < 10 -- number of times to repeat the action

)

Select    * From    T

Option (MaxRecursion 0);


An example: Get a list of all of the letters:
With T As
(
Select N'a' Letter, -- first letter
N'bcdefghijklmnopqrstuvwxyz' Letters
Union All
Select LEFT(Letters, 1) Letter, 
RIGHT(Letters, len(Letters)-1) Letters
From T
Where len(Letters)>0
)
Select Letter
From    T
Option (MaxRecursion 0);