Performance test - select vs while - Get all days between two dates., Sql Server

Performance test - select vs while - Get all days between two dates.

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

DECLARE @Date1 DATE, @Date2 DATE, @DATE_NOW DATETIME = GETDATE();
SET @Date1 = '2017-01-01';
SET @Date2 = '2018-01-01';

CREATE TABLE #TEMP_DATES_TEST
(
    DATE DATETIME
);

INSERT INTO #TEMP_DATES_TEST
SELECT DATEADD(DAY ,number,@Date1) [DATE]
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(DAY,number,@Date1) <= @Date2;

SELECT 'QUERY 1 - TAKEN ' + CONVERT(VARCHAR(50), DATEDIFF(MS, @DATE_NOW, GETDATE())) + 'MS';

SET @DATE_NOW = GETDATE();

DECLARE @DAYS INT, @COUNTER INT = 0;
SET @DAYS = DATEDIFF(DAY, '2017-01-01','2018-01-01');
SELECT @DAYS;

CREATE TABLE #TEMP_DATES
(
    DATE DATETIME
);

WHILE @DAYS > 0
BEGIN
    SET @DAYS = @DAYS - 1;
    INSERT INTO #TEMP_DATES
        SELECT DATEADD(DAY, @COUNTER ,'2017-01-01');
        
    SET @COUNTER = @COUNTER + 1;
        
END;
     
--select 'DAYS = ' + convert(varchar(3), @DAYS);
--select 'COUNTER = ' + convert(varchar(3), @COUNTER);
--SELECT * FROM #TEMP_DATES;
SELECT 'QUERY 2 (MORE SLOWER) - TAKEN ' + CONVERT(VARCHAR(50), DATEDIFF(MS, @DATE_NOW, GETDATE())) + 'MS';

DROP TABLE #TEMP_DATES;
DROP TABLE #TEMP_DATES_TEST;


run \| edit \| history \| help	0

λ

                                      .NET NoSQL database for rapid development