4 ms·

If your DB doesn't support this technique, you might be able to use this rather disgusting technique, which builds an exponentially growing tower of rows by usi

If your DB doesn't support this technique, you might be able to use this rather disgusting technique, which builds an exponentially growing tower of rows by using nested queries with JOIN's. https://stackoverflow.com/a/61169467 https://stackoverflow.com/a/61169467

dspillett 6y ago
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.

dspillett 6y ago

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.