WITH numbers(n) AS
  SELECT row_number() OVER (ORDER BY object_id) FROM sys.columns
)
SELECT n FROM numbers WHERE n <= 100

The row_number function is handy for a lot of things.

If you want lots of numbers, you not have a good table to work from. Here
is a query for a million numbers:

CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY);
WITH digits (d) AS (
  SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
  SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION
  SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION
  SELECT 0)
INSERT Numbers (Number)
  SELECT Number
  FROM   (SELECT i.d + ii.d * 10 + iii.d * 100 + iv.d * 1000 +
                 v.d * 10000 + vi.d * 100000 AS Number
          FROM   digits i
          CROSS  JOIN digits ii
          CROSS  JOIN digits iii
          CROSS  JOIN digits iv
          CROSS  JOIN digits v
          CROSS  JOIN digits vi) AS Numbers
  WHERE  Number > 0

Here is a method using the system table spt_values
(http://www.sqlmag.com/Articles/ArticleID/22920/pg/2/2.html), which works
fine for small numbers table (note: the table is undocumented and assuming
unsupported). This works fine on SQL Server 2000, 2005, and the current 2008
CTP:

CREATE TABLE Numbers (nbr INT NOT NULL PRIMARY KEY)

INSERT INTO Numbers ( nbr )
SELECT number FROM master..spt_values
WHERE type = 'P'
  AND number BETWEEN 1 AND 100

HTH,

Plamen Ratchev
http://www.SQLStudio.com