The concept of a CTE, or common table expression, is something new in SQL 2005, and it is hugely powerful for things such as traversing trees or hierarchies, among other things.

You can use a CTE to generate an ad-hoc Numbers Table.  Try this:

WITH Numbers(n)
AS
(
SELECT 1 AS n
UNION ALL
SELECT (n + 1) AS n
FROM Numbers
WHERE
n < 1000
)
SELECT n FROM Numbers 
OPTION(MAXRECURSION 1000) 

That will generate numbers to 1000.  Change the comparison expression, and raise up the max recursion depth, to go to a higher number.  Or, instead of using the CTE in your SQL, you could just do a SELECT INTO and dump it into a table.

If your not sure why you would want a numbers table, just think about it a bit, or go Google!  A numbers table is fantastically useful for all sorts of tasks in a database, including parsing text, joining against to create multiples, and more.