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
- The functions accepts a Starting point and an Ending point.
- 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
Legacy Comments
|
Brad Schulz
2009-11-03 |
re: Superfast sequence generators! Great stuff, as always, Peso! |
|
Peso
2009-11-03 |
re: Superfast sequence generators! Thank you Brad. |
|
online automat
2010-06-16 |
re: Superfast sequence generators! wow this is a very nice little tutorial. Thank you for posting it. and great blog, btw... |
|
dineshrajan
2010-08-06 |
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 |