[dspillett]

Some DBs support "literal tables" which makes this a little less nasty:

    SELECT n FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS d(n)

for instance:

        SELECT seq = units.n + tens.n*10 + hundreds.n*100 + thousands.n*1000
          FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS units(n)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS tens(n)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS hundreds(n)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS thousands(n)
         WHERE units.n + tens.n*10 + hundreds.n*100 + thousands.n*1000 BETWEEN 1 AND 1337

You can make it neater still by making the sub-table with a CTE:

    WITH Digits AS (SELECT n FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS d(n))
            SELECT seq = units.n + tens.n*10 + hundreds.n*100 + thousands.n*1000
              FROM Digits AS units
        CROSS JOIN Digits AS tens
        CROSS JOIN Digits AS hundreds
        CROSS JOIN Digits AS thousands
             WHERE units.n + tens.n*10 + hundreds.n*100 + thousands.n*1000 BETWEEN 1 AND 1337

or

    WITH Digits AS (SELECT n FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS d(n))
    , Thousands AS (SELECT seq = units.n + tens.n*10 + hundreds.n*100 + thousands.n*1000
              FROM Digits AS units
        CROSS JOIN Digits AS tens
        CROSS JOIN Digits AS hundreds
        CROSS JOIN Digits AS thousands
        )
    SELECT seq FROM thousands ORDER BY seq
     WHERE units.n + tens.n*10 + hundreds.n*100 + thousands.n*1000 BETWEEN 1 AND 1337

though at that point you probably have the recursive option so you'd need to test to see which is more efficient. As a further hack you can combine this and the recursive CTE to extend the number of rows you can return when recursion limits would otherwise be a problem.

You can also use simple window functions to make a sequence from a system table that you know will have a few thousand rows:

    SELECT n=ROW_NUMBER() OVER (ORDER BY object_id) FROM sys.all_columns

Or if you just need a few thousand rows to hang randomly generated data from you don't even need the function.

All a bit hacky compared to some DBs that have sequence support, but useful for generating test data or numbers tables where that isn't supported.