This is a quick script I use to quickly build a tally table. If you need more numbers, you can add more joins to the Tally CTE.
( select n
from (values (1), (2), (3), (4), (5), (6), (7), (8), (9), (0)
, cteHundreds (n)
select a.n from cteTens a, cteTens b
, cteTally (n)
select n = row_number() over (order by (select null))
from cteHundreds a, cteHundreds b
The Tally Table has proven to be a simple and elegant method for avoiding many varieties of RBAR. Unfortunately, one of its more common uses, that of a CSV splitter, has a well-known and serious performance problem. MVP Jeff Moden shows us what that problem is and how to correct it. (UPDATED with additional info and attachments on 5/12/2011).