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