Now that I have several posts on what you can do with a Tally table, I figured I'd share my favorite way to create one inline.  I still prefer to have a physical tally table (usually in a Utility database that can be accessed from anywhere and doesn't need to be created in each individual database) for permament code, but for times when you need one on the fly, this is my preferred method.  I can't really take the credit for this query, the base construct is based on something I've seen attributed to Itzik Ben-Gan.   I've modified it a bit and changed up the formatting to be the way I like it.  Anything over a few thousand rows I'd probably use a physical tally table for, but on small numbers you shouldn't see much of a performance hit with this script.

-- Tally Table CTE script (SQL 2005+ only)
-- You can use this to create many different numbers of rows... for example:
-- You could use a 3 way cross join (t3 x, t3 y, t3 z) instead of just 2 way to generate a different number of rows.
-- The # of rows this would generate for each is noted in the X3 comment column below.
-- For most common usage, I find t3 or t4 to be enough, so that is what is coded here.
-- If you use t3 in ‘Tally’, you can delete t4 and t5.


; WITH
    -- Tally table Gen            Tally Rows:     X2                X3
t1 AS (SELECT 1 N UNION ALL SELECT 1 N),    -- 4            ,    8
t2 AS (SELECT 1 N FROM t1 x, t1 y),            -- 16            ,    64
t3 AS (SELECT 1 N FROM t2 x, t2 y),            -- 256            ,    4096
t4 AS (SELECT 1 N FROM t3 x, t3 y),            -- 65536        ,    16,777,216
t5 AS (SELECT 1 N FROM t4 x, t4 y),            -- 4,294,967,296,    A lot
Tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N
          FROM t3 x, t3 y) -- Change the t3's to one of the other numbers above for more/less rows