Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

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 ]

Feedback

Gravatar

# re: Superfast sequence generators!

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

# re: Superfast sequence generators!

Thank you Brad.
11/3/2009 7:21 PM | Peso
Gravatar

# 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
Gravatar

# 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.

Powered by:
Powered By Subtext Powered By ASP.NET