How to create a calendar table in SQL Server

I wanted a calendar table in SQL Server (Azure SQL Database), so here is a memo on how to create one.

I googled, and it seems to be created by creating a table with "WITH" and then making a recursive call from "UNION ALL" in the table definition.

WITH

_calendar AS
(
SELECT
    CAST(N'2019-01-01' AS DATE) AS dt
UNION ALL
SELECT
    DATEADD(DAY, 1, _tbl.dt) AS dt  /* Date interval is adjusted here */
FROM _calendar AS _tbl
WHERE
    _tbl.dt < N'2019-12-31'
)

SELECT
*
FROM _calendar
OPTION ( MAXRECURSION 0 )  /* recursion unlimited */

It is like starting with the first SELECT, and then linking with UNION ALL.

Below UNION ALL, FROM contains one record of the result of the previous SELECT and the condition check of WHERE.

If the conditions are met, the SELECT is calculated and a record is created, which continues recursively to the next UNION ALL, FROM, and so on.

And when the condition of WHERE is no longer met, the program terminates.

The number of recurrences is limited to 100 by default and is optionally set to 0 for unlimited.

This would also allow us to make sequential numbers.

/* 10 jump numbers from 10 to 1000 */
WITH

_seq AS
(
SELECT
  10 AS num
UNION ALL
SELECT
  _tbl.num + 10 AS num
FROM _seq AS _tbl
WHERE _tbl.num<=1000-10
)

SELECT
*
FROM _seq
OPTION ( MAXRECURSION 0 )

experiment

However, I was a bit confused about how the recursion was actually called, so I put three records of ['2019-01-01', '2019-02-01', '2019-03-01'] at the start and experimented

WITH

tbl AS
(
    (
        SELECT
            _a.v
        FROM (VALUES
            (CAST(N'2019-01-01' AS DATE)),
            (CAST(N'2019-02-01' AS DATE)),
            (CAST(N'2019-03-01' AS DATE))
            ) AS _a(v)
    )
    UNION ALL
    (
        SELECT
            DATEADD(DAY, 1, _b.v) AS v
        FROM tbl AS _b
        WHERE DAY(_b.v) <= 2
    )
)

SELECT * FROM tbl

The result is...

v
2019-01-01
2019-02-01
2019-03-01
2019-03-02
2019-03-03
2019-02-02
2019-02-03
2019-01-02
2019-01-03

After the output of the start, the value of the start,broken down into record units andThe looping was done under UNION ALL. The table is not just passed to UNION ALL and below.