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
Post a comment or leave a trackback: Trackback URL.

Trackbacks

  • […] We’ll also make use of our numbers function to simulate the “one” side of our “one-to-many” data set. The number data will join to the foreign key (FK) we created in the demo table, which serves as our “many” side. Wait — you don’t have a “numbers” function? Get one here. […]

  • By Shredding XML | T-SQL Ref on 12 May 2014 at 0322

    […] that utfNumbers in the FROM statement? You mean you don’t have one of those? Take a look here to see the value and roll your […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: