Monthly Archives: November 2011

utfNumbers

A helpful function borrowed from Itzik Ben-Gan; a table of numbers comes in very handy, especially when it takes virtually no disk space.

-- Create a table-valued function
CREATE FUNCTION
 dbo.utfNumbers (
      @n AS BIGINT
)
RETURNS TABLE
AS
RETURN
WITH
      L0 AS (SELECT 1 AS c UNION ALL SELECT 1)
      , L1 AS (SELECT 1 AS c FROM L0 AS a, L0 AS b)
      , L2 AS (SELECT 1 AS c FROM L1 AS a, L1 AS b)
      , L3 AS (SELECT 1 AS c FROM L2 AS a, L2 AS b)
      , L4 AS (SELECT 1 AS c FROM L3 AS a, L3 AS b)
      , L5 AS (SELECT 1 AS c FROM L4 AS a, L4 AS b)
      , Nums AS (
            SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS n
            FROM L5)
      SELECT TOP(@n) n FROM Nums ORDER BY n
;

To use the function, simply select ‘n’ from the function as though it was a regular table, passing in as a parameter the total number of numbers you want returned. The example below will return 100 rows containing one column of values from one to 100.

-- Display content of the function
SELECT
n
FROM dbo.utfNumbers(100)
;

Advertisements