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:
- 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)
- 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
- 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
- Utilise function without a CTE but using the OVER and ROW_NUMBER() clauses
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
|
