# Thinking outside the box

Patron Saint of Lost Yaks

## Superfast sequence generators!

Based on Itzik's suggestion for sequence generators, I rewrote them and adapted them for my needs.
Itzik showed how to work around a big issue for some cases where the query optimizer actually tried to produce all possible combinations before returning the wanted records.

My rewrite is two-part

1) The functions accepts a Starting point and an Ending point.
2) The functions accepts a Stepping point. You may want only odd or even numbers? Or only every 7th day?

Here are the functions. Have fun with them!
I haven't checked performance compared to MVJ's F_NUMBER_TABLE function, but they should be at least equally fast.

CREATE FUNCTION dbo.fnGetNumbers
(
@FromNum INT,
@ToNum INT,
@Step INT
)
RETURNS TABLE
AS
RETURN
WITH    n0 AS (SELECT 1 AS p UNION ALL SELECT 1),
n1 AS (SELECT 1 AS p FROM n0 AS a CROSS JOIN n0 AS b),
n2 AS (SELECT 1 AS p FROM n1 AS a CROSS JOIN n1 AS b),
n3 AS (SELECT 1 AS p FROM n2 AS a CROSS JOIN n2 AS b),
n4 AS (SELECT 1 AS p FROM n3 AS a CROSS JOIN n3 AS b),
n5 AS (SELECT 1 AS p FROM n4 AS a CROSS JOIN n4 AS b)

SELECT  @FromNum + @Step * n AS Number
FROM    (
SELECT  TOP (1 +(@ToNum - @FromNum) / @Step)
ROW_NUMBER() OVER (ORDER BY p) - 1 AS n
FROM    n5
) AS d
GO

CREATE FUNCTION dbo.fnGetDates
(
@FromDate AS DATETIME,
@ToDate DATETIME,
@Step INT
)
RETURNS TABLE
AS
RETURN
WITH    d0 AS (SELECT 1 AS p UNION ALL SELECT 1),
d1 AS (SELECT 1 AS p FROM d0 AS a CROSS JOIN d0 AS b),
d2 AS (SELECT 1 AS p FROM d1 AS a CROSS JOIN d1 AS b),
d3 AS (SELECT 1 AS p FROM d2 AS a CROSS JOIN d2 AS b),
d4 AS (SELECT 1 AS p FROM d3 AS a CROSS JOIN d3 AS b),
d5 AS (SELECT 1 AS p FROM d4 AS a CROSS JOIN d4 AS b)

SELECT  DATEADD(DAY, n * @Step, @FromDate) AS [Date]
FROM    (
SELECT  TOP (1 + DATEDIFF(DAY, @FromDate, @ToDate) / @Step)
ROW_NUMBER() OVER (ORDER BY p) - 1 AS n
FROM    d5
) AS d
GO

Print | posted on Tuesday, November 03, 2009 11:38 AM | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

## #re: Superfast sequence generators!

Great stuff, as always, Peso!
11/3/2009 6:57 PM | Brad Schulz

## #re: Superfast sequence generators!

11/3/2009 7:21 PM |

## #re: Superfast sequence generators!

wow this is a very nice little tutorial. Thank you for posting it.

and great blog, btw...
6/16/2010 7:28 AM | online automat

## #re: Superfast sequence generators!

DECLARE @STEP INT = 3
DECLARE @START INT =50
DECLARE @END INT =100

SELECT T.REC
FROM
(
SELECT [REC]= @START + @STEP * R.RowId
FROM
(
SELECT TOP 100 [RowId]= ROW_NUMBER() OVER(ORDER BY M.NUMBER) -1
FROM
master..SPT_VALUES M
CROSS JOIN
master..SPT_VALUES M1
) R
) T
WHERE T.REC < @END
8/6/2010 1:56 PM | dineshrajan
Comments have been closed on this topic.