You may want to avoid the disk space and un-cached I/O involved in using a numbers table. These techniques produce the number of numbers you want quick and easy.
Note that there is a limit to the number of rows this can return but depending on the table you use as a source the rows may be cached already.
In this case, with a clean install of 2008 (no tables defined yet), I can generate over 25M numbers.
By adding another param and a simple addition to the function’s query you can return any range. For example “select n from Numbers(-32768, 32767)”.
-- Returns numbers from 0 up to (but not including) @COUNT -- A decent replacement for master.dbo.spt_values when you need more than 0-2047 CREATE FUNCTION Numbers (@COUNT int) RETURNS TABLE AS RETURN SELECT TOP (SELECT @COUNT) ROW_NUMBER() OVER (ORDER BY (SELECT 1)) -1 AS N FROM master.sys.all_columns A, master.sys.all_columns B;
If you can’t read master.sys, typically for security reasons, you can use almost any table with lots of rows. But you can also create numbers another way. The following produces up to 100,000 numbers very quickly and is easily expandable. In some circumstances this technique can be faster than a physical table. The drawback is that the source is virtual and therefore never in cache but with no I/O ever needed it’s performance is reliable.
-- Returns numbers from 0 up to (but not including) @COUNT -- This tops out at 100,000. Adding N6 would yield 1,000,000 values etc. -- Adding more joins does slow the function just a bit so creating several -- and using the one with the capacity you need yields top performance. CREATE FUNCTION Numbers (@COUNT int) RETURNS TABLE AS RETURN WITH N(I)AS ( SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) SELECT TOP (SELECT @COUNT) ROW_NUMBER() OVER (ORDER BY (SELECT 1)) -1 AS N FROM N N1, N N2, N N3, N N4, N N5;
Update: Leave it to that genius Itzik Ben-Gan to have another/faster way.
CREATE FUNCTION dbo.GetNums(@low AS BIGINT, @high AS BIGINT) RETURNS TABLE AS RETURN WITH L0 AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)), L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B), L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B), L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B), L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B), L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B), Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum FROM L5) SELECT TOP(@high – @low + 1) @low + rownum – 1 AS n FROM Nums ORDER BY rownum;