How to write a query that returns a list of numbers in T-SQL

Suppose you want to write a query that returns the integers from 0 to 4. As shown on stackoverflow a recursive common table expression (CTE) can be used. A recursive CTE includes references to itself:

WITH Numbers
AS
(
-- anchor member
SELECT 0 AS Number
-- set operator
UNION ALL
-- recursive member
SELECT Number + 1 FROM Numbers WHERE Number < 4
)
SELECT Number FROM Numbers;

which outputs

0

1

2

3

4

Here is how the list is created:
Step 1: The anchor member query runs and returns 0.
Step 2: Since 0 is less than 4, the recursive member query adds 1 to 0 and returns 1.
Step 3: Since 1 is less than 4, the recursive member query adds 1 to 1 and returns 2.
Step 4: Since 2 is less than 4, the recursive member query adds 1 to 2 and returns 3.
Step 5: Since 3 is less than 4, the recursive member query adds 1 to 3 and returns 4.
Step 6: Since 4 is not less than 4, the recursive member query returns NULL and the loop terminates.