Cool Tips‎ > ‎Data Conversion‎ > ‎

Data Conversion - 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
  3. 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
  4. 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



ċ
NumbersTable function (with CTE).sql
(0k)
Andy Hughes,
Jun 7, 2012, 10:54 AM
ċ
NumbersTable function (without CTE).sql
(2k)
Andy Hughes,
Jun 7, 2012, 10:54 AM
Comments