Create date table of every second between two dates

I need to create a table of every second between two input dates

eg between 01/01/2015 00:00:01 and 05/01/2016 00:00:00

So a 3m row expected output that looks like this:

Does this need a CLR? function. Does anyone have any examples?

Answer

A Tally will be (by far) the fastest method here. Though you can do this with an rCTE as well, if you are going to be doing this for 100,000’s (or even millions) of seconds, it’s going to get slow fast.

DECLARE @StartDate datetime2(0) = '2020-01-01T17:00:00',
        @EndDate datetime2(0) = '2020-03-02T01:30:45';

WITH N AS (
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS (
    SELECT TOP (DATEDIFF_BIG(SECOND,@StartDate,@EndDate)+1)
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1  AS I
    FROM N N1, N N2,  N N3,  N N4, N N5, N N6, N N7, N N8, N N9) --Up to 1B rows (a little under under 32 years)
SELECT DATEADD(SECOND,T.I,@StartDate)
FROM Tally T;