SQL Server - Efficient generation of dates in a range

Using SQL Server 2016.

I have a stored procedure that produces a list of options against a range of dates. Carriage options against days for clarity but unimportant to the specifics here.

The first step in the stored procedure generates a list of dates to store additional data against, and generating this list is taking substantially longer than the balance of the code. While this process is individual short, the number of calls means that this one piece of code is putting the system under more load than anything else.

With that in mind I have been testing efficiency of several options.

Iterative common table expression:

CREATE FUNCTION [dbo].[udf_DateRange_CTE] (@StartDate DATE,@EndDate DATE)
RETURNS @Return TABLE (Date DATE NOT NULL)
AS
    BEGIN
        WITH    dates(date)
                  AS (SELECT    @StartDate [Date]
                      UNION ALL
                      SELECT    DATEADD(dd, 1, [Date])
                      FROM      dates
                      WHERE     [Date] < @EndDate
                     )
            INSERT  INTO @Return 
            SELECT  date
            FROM    dates
            OPTION  (MAXRECURSION 0)
        RETURN 
    END

A while loop:

CREATE FUNCTION [dbo].[udf_DateRange_While] (@StartDate DATE,@EndDate DATE)
RETURNS @Retun TABLE (Date DATE NOT NULL,PRIMARY KEY (Date))
AS
    BEGIN
        WHILE @StartDate <= @EndDate
            BEGIN
                INSERT  INTO @Retun
                VALUES  (@StartDate)
                SET @StartDate = DATEADD(DAY,1,@StartDate)
            END
        RETURN 
    END

A lookup from a pre-populated table of dates:

CREATE FUNCTION [dbo].[udf_DateRange_query] (@StartDate DATE,@EndDate DATE)
RETURNS @Return TABLE (Date DATE NOT NULL)
AS
    BEGIN
        INSERT  INTO @Return
        SELECT  Date
        FROM    DateLookup
        WHERE   Date >= @StartDate
                AND Date <= @EndDate
        RETURN 
    END

In terms of efficiency I have test generating a years worth of dates, 1000 times and had the following results:

CTE: 10.0 Seconds
While: 7.7 Seconds
Query: 2.6 Seconds

From this the query is definitely the faster option but does require a permanent table of dates that needs to be created and maintained. This means that the query is no loner "self-contained" and it would be possible to request a date outside of the given date range.

Does anyone know of any more efficient ways of generating dates for a range, or any optimisation I can apply to the above?

Many thanks.

Solution

You can try like following. This should be fast compared CTE or WHILE loop.

DECLARE @StartDate DATETIME = Getdate() - 1000 
DECLARE @EndTime DATETIME = Getdate() 

SELECT * 
FROM   (SELECT @StartDate + RN AS DATE 
        FROM   (SELECT ROW_NUMBER() 
                         OVER ( 
                           ORDER BY (SELECT NULL)) RN 
                FROM   master..[spt_values]) T) T1 
WHERE  T1.DATE <= @EndTime 
ORDER  BY DATE

Note: This will work for day difference <= 2537 days

If you want to support more range, you can use CROSS JOIN on master..[spt_values] to generate range between 0 - 6436369 days like following.

DECLARE @StartDate DATETIME = Getdate() - 10000
DECLARE @EndTime DATETIME = Getdate() 
SELECT @StartDate + RN AS DATE FROM
(   
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN 
    FROM   master..[spt_values] T1
    CROSS JOIN  master..[spt_values] T2
) T 
WHERE RN <= DATEDIFF(DAY,@StartDate,@EndTime)