Creating a Calendar table without recursion

Question

I want to create a Calendar Table without using the recursion as I have prepared earlier. How can I achieve this task. All the required columns are mentioned in code below and few other details in code comments. ...........................................................................................................................................................................................................................................................................

DECLARE @StartDate  date = '20200101'
DECLARE @CutoffDate date = GETDATE()

;WITH seq(n) AS 
(
  SELECT 0 UNION ALL SELECT n + 1 FROM seq
  WHERE n < DATEDIFF(DAY, @StartDate, @CutoffDate)
),
d(d) AS 
(
  SELECT DATEADD(DAY, n, @StartDate) FROM seq
),
src AS      /*SOURCE TABLE WITH OBJECT DEFINITION*/
(
  SELECT
    TheDate         = CONVERT(date, d),
    TheDay          = DATEPART(DAY,       d),
    TheDayName      = DATENAME(WEEKDAY,   d),
    TheWeek         = DATEPART(WEEK,      d),
    TheDayOfWeek    = DATEPART(WEEKDAY,   d),
    TheMonth        = DATEPART(MONTH,     d),
    TheMonthName    = DATENAME(MONTH,     d),
    TheQuarter      = Concat('Q',DATEPART(Quarter,   d)),
    Financial_Year  = DATEPART(YEAR,      d),
    Financial_Quarter=Datepart(QUARTER,d),
    TheYear         = DATEPART(YEAR,      d),
    TheFirstOfMonth = DATEFROMPARTS(YEAR(d), MONTH(d), 1),
    TheFirstOfFYear   = DATEFROMPARTS(YEAR(d), 4, 1),
    TheFirstOfYear   = DATEFROMPARTS(YEAR(d), 1, 1),
    TheLastOfYear   = DATEFROMPARTS(YEAR(d), 12, 31),
    TheDayOfYear    = DATEPART(DAYOFYEAR, d)
  FROM d
),
Dimension AS
(
  SELECT
    TheDate, 
    TheDay,
    TheDayName,
    TheDayOfWeek,
    TheDayOfWeekInMonth = CONVERT(tinyint, ROW_NUMBER() OVER 
                            (PARTITION BY TheFirstOfMonth, TheDayOfWeek ORDER BY TheDate)),
    TheDayOfYear,
    TheWeek,
    TheFirstOfWeek      = DATEADD(DAY, 1 - TheDayOfWeek, TheDate),
    TheLastOfWeek       = DATEADD(DAY, 6, DATEADD(DAY, 1 - TheDayOfWeek, TheDate)),
    TheWeekOfMonth      = CONVERT(tinyint, DENSE_RANK() OVER 
                            (PARTITION BY TheYear, TheMonth ORDER BY TheWeek)),
    TheMonth,
    TheMonthName,
    TheFirstOfMonth,
    TheLastOfMonth      = MAX(TheDate) OVER (PARTITION BY TheYear, TheMonth),
    TheFirstOfNextMonth = DATEADD(MONTH, 1, TheFirstOfMonth),
    TheLastOfNextMonth  = DATEADD(DAY, -1, DATEADD(MONTH, 2, TheFirstOfMonth)),
    TheQuarter,
    TheFirstOfQuarter   = MIN(TheDate) OVER (PARTITION BY TheYear, TheQuarter),
    TheLastOfQuarter    = MAX(TheDate) OVER (PARTITION BY TheYear, TheQuarter),
    TheYear,
    TheFirstOfYear      = DATEFROMPARTS(TheYear, 1,  1),
    TheFirstOfFYear     = DATEFROMPARTS(TheYear, 4,  1),
    TheLastOfYear,
    MMYYYY              = CONVERT(char(2), CONVERT(char(8), TheDate, 101))
                          + CONVERT(char(4), TheYear),
                            

    Financial_Quarter   = Datepart(Quarter,DATEADD(MONTH, -3, TheFirstOfMonth)), /*Starting Financial Quarter from April*/
    Financial_Year      =CASE
                            WHEN Financial_Quarter = 1 THEN DATEPART(Year,Dateadd(Year,-1,TheFirstofYear)) ELSE THEYEAR END

  FROM src
)
SELECT * FROM Dimension
  ORDER BY TheDate
  OPTION (MAXRECURSION 0);

Answer 1

As I mentioned in the comments, use a Tally. These are significantly faster than a rCTE as they aren't recursive. I use an inline tally here:

DECLARE @StartDate date = '20200101';
DECLARE @CutoffDate date = GETDATE();
/*
; is a terminator, not a "beginingator". It goes at the end of ALL your statements,
not at the start of statements that require the PREVIOUS statement to be properly terminated.
*/
WITH N AS 
    (SELECT N
     FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) N(N)),
Tally AS
    (SELECT 0 AS I
     UNION ALL
     SELECT TOP (DATEDIFF(DAY, @StartDate, @CutoffDate))
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
     FROM N N1,N N2,N N3), --Up to 1000 rows. Add more cross joins for more rows
D AS
    (SELECT DATEADD(DAY, T.I, @StartDate) AS d
     FROM Tally T),
Src AS /*SOURCE TABLE WITH OBJECT DEFINITION*/
    (SELECT CONVERT(date, d) AS TheDate,
            DATEPART(DAY, d) AS TheDay,
            DATENAME(WEEKDAY, d) AS TheDayName,
            DATEPART(WEEK, d) AS TheWeek,
            DATEPART(WEEKDAY, d) AS TheDayOfWeek,
            DATEPART(MONTH, d) AS TheMonth,
            DATENAME(MONTH, d) AS TheMonthName,
            CONCAT('Q', DATEPART(QUARTER, d)) AS TheQuarter,
            DATEPART(YEAR, d) AS Financial_Year,
            DATEPART(QUARTER, d) AS Financial_Quarter,
            DATEPART(YEAR, d) AS TheYear,
            DATEFROMPARTS(YEAR(d), MONTH(d), 1) AS TheFirstOfMonth,
            DATEFROMPARTS(YEAR(d), 4, 1) AS TheFirstOfFYear,
            DATEFROMPARTS(YEAR(d), 1, 1) AS TheFirstOfYear,
            DATEFROMPARTS(YEAR(d), 12, 31) AS TheLastOfYear,
            DATEPART(DAYOFYEAR, d) AS TheDayOfYear
     FROM d),
Dimension AS
    (SELECT TheDate,
            TheDay,
            TheDayName,
            TheDayOfWeek,
            CONVERT(tinyint, ROW_NUMBER() OVER (PARTITION BY TheFirstOfMonth, TheDayOfWeek ORDER BY TheDate)) AS TheDayOfWeekInMonth,
            TheDayOfYear,
            TheWeek,
            DATEADD(DAY, 1 - TheDayOfWeek, TheDate) AS TheFirstOfWeek,
            DATEADD(DAY, 6, DATEADD(DAY, 1 - TheDayOfWeek, TheDate)) AS TheLastOfWeek,
            CONVERT(tinyint, DENSE_RANK() OVER (PARTITION BY TheYear, TheMonth ORDER BY TheWeek)) AS TheWeekOfMonth,
            TheMonth,
            TheMonthName,
            TheFirstOfMonth,
            MAX(TheDate) OVER (PARTITION BY TheYear, TheMonth) AS TheLastOfMonth,
            DATEADD(MONTH, 1, TheFirstOfMonth) AS TheFirstOfNextMonth,
            DATEADD(DAY, -1, DATEADD(MONTH, 2, TheFirstOfMonth)) AS TheLastOfNextMonth,
            TheQuarter,
            MIN(TheDate) OVER (PARTITION BY TheYear, TheQuarter) AS TheFirstOfQuarter,
            MAX(TheDate) OVER (PARTITION BY TheYear, TheQuarter) AS TheLastOfQuarter,
            TheYear,
            DATEFROMPARTS(TheYear, 1, 1) AS TheFirstOfYear,
            DATEFROMPARTS(TheYear, 4, 1) AS TheFirstOfFYear,
            TheLastOfYear,
            CONVERT(char(2), CONVERT(char(8), TheDate, 101)) + CONVERT(char(4), TheYear) AS MMYYYY,
            DATEPART(QUARTER, DATEADD(MONTH, -3, TheFirstOfMonth)) AS Financial_Quarter, /*Starting Financial Quarter from April*/
            CASE
                 WHEN Financial_Quarter = 1 THEN DATEPART(YEAR, DATEADD(YEAR, -1, TheFirstOfYear))
                 ELSE TheYear
            END AS Financial_Year
     FROM src)
SELECT *
FROM Dimension
ORDER BY TheDate;