Generate Numbers Table

Applicability:

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: Unknown

Date: 22 Jan 2011

Description

Increasingly over the past few years, I have seen coding techniques that utilise a 'Numbers' table in a join, particularly when using Common Table Expression (CTE) queries to create, identify, order or repair ranges of data. Data paging or creation of date ranges are two specific examples.

I'm not going to concentrate on those specific use cases here, but on the generation of the Numbers table in the first place.

There are several approaches that can be used:

    1. Create a static physical lookup table

      • Needs to be present on every server that utilises the data

      • Needs to contain a number range that would cover every potential use case

      • Can be a large table with millions of rows (but with a very small disk footprint)

    2. Utilise a function encapsulating a CTE

      • Fast

      • Portable (generates only the required range on-demand)

      • Efficient (low logical reads, zero physical reads and low scan count)

    • Can only be used on SQL 2005 and greater

    1. Utilise a function without a CTE

      • Fast

      • Portable (generates only the required range on-demand)

      • Very Efficient (very low logical reads, zero physical reads and low scan count)

    • Can be used on all version of SQL Server

    1. Utilise function without a CTE but using the OVER and ROW_NUMBER() clauses

      • Fast

      • Portable

      • Very Efficient (very low logical reads, zero physical reads and low scan count)

    • Can only be used on SQL 2005 and greater

The code below covers scenarios #2, #3 and #4 only, as these are the most portable end efficient solutions.

The stated efficiencies should be taken as a guide only and tested in your own environments, but I have found no detrimental effects to either approach.

Code

Function Encapsulating CTE (SQL 2005 and above):

DROP FUNCTION dbo.udf_NumbersTable

GO

CREATE FUNCTION dbo.udf_NumbersTable

(

@fromNumber INT,

@toNumber INT,

@byStep INT

)

RETURNS TABLE

RETURN

(

WITH CTE_NumbersTable AS

(

SELECT @fromNumber AS i

UNION ALL

SELECT i + @byStep

FROM CTE_NumbersTable

WHERE (i + @byStep) <= @toNumber

)

SELECT * FROM CTE_NumbersTable

)

--select * from dbo.udf_NumbersTable(75,100,1)

Function without CTE (All SQL Versions)

DROP TABLE Digits

GO

CREATE TABLE Digits (value INT)

INSERT Digits (value) SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9

DROP FUNCTION dbo.udf_NumbersTable

GO

CREATE FUNCTION dbo.udf_NumbersTable

(

@first int , --##PARAM @first The lowest value in the range.

@last int --##PARAM @last The highest value in the range.

)

RETURNS @values TABLE

(

value int primary key

)

AS

BEGIN

INSERT INTO @values

(

value

)

SELECT

num = units.value +

(tens.value) +

(hundreds.value ) +

(Thousands.value ) +

(TenThousands.value ) +

(CThousands.value ) +

(Millions.value )

FROM dbo.Digits units

CROSS JOIN (SELECT value * 10 as value from dbo.Digits WHERE value * 10 <=@last) tens

CROSS JOIN (SELECT value * 100 as value from dbo.Digits WHERE value * 100 <=@last) hundreds

CROSS JOIN (SELECT value * 1000 as value from dbo.Digits WHERE value * 1000 <=@last) Thousands

CROSS JOIN (SELECT value * 10000 as value from dbo.Digits WHERE value * 10000 <=@last) TenThousands

CROSS JOIN (SELECT value * 100000 as value from dbo.Digits WHERE value * 100000 <=@last) CThousands

CROSS JOIN (SELECT value * 1000000 as value from dbo.Digits WHERE value * 1000000 <=@last) Millions

WHERE

units.value +

(tens.value ) +

(hundreds.value) +

(Thousands.value ) +

(TenThousands.value ) +

(CThousands.value ) +

(Millions.value )

BETWEEN @first and @last

RETURN

END

GO

-- select * from dbo.udf_NumbersTable (75,100)

Function using ROW_NUMBER and OVER clauses (SQL 2005 and above)

DROP FUNCTION dbo.udf_NumbersTable

GO

-- select * from dbo.udf_NumbersTable (75,100)

CREATE FUNCTION dbo.udf_NumbersTable

(

@first INT , --##PARAM @first The lowest value in the range.

@last INT --##PARAM @last The highest value in the range.

)

RETURNS @values TABLE

(

value INT PRIMARY KEY

)

AS

BEGIN

INSERT INTO @values

(

value

)

SELECT Numbers.N

FROM

(

SELECT ROW_NUMBER() OVER (ORDER BY a.OBJECT_ID)AS N

FROM sys.columns a, sys.columns b

) AS Numbers

WHERE Numbers.N BETWEEN @first AND @last

RETURN

END

GO