Interesting.

Or old school:

select distinct Number from master.dbo.spt_values where number between 0 and 1000

Another one I have seen is:

;With CTE ( [Number] ) as (

select Number = '0'

union select Number = '1'

union select Number = '2'

union select Number = '3'

union select Number = '4'

union select Number = '5'

union select Number = '6'

union select Number = '7'

union select Number = '8'

union select Number = '9'

)

Select [Number]

from (

select [Number] = cast([3sp].[Number] + [2sp].[Number] + [1sp].[Number] as varchar(10))

from CTE as [1sp]

cross join CTE as [2sp]

cross join CTE as [3sp]

) as x

where [Number] between 0 and 1000

order by 1

But the best in a way is a tally table which can be indexed and used in a query plan.

A slight problem that you might have with a recursive CTE is that the ".....the MAXRECURSION option exceeds the allowed maximum of 32767"