Generate range of constants in SQL Server

Question

Is there a way to select a range of constants, such as every integer between 1 and 100, or every month between two dates?

Instead of doing this...

select '2010-01-01' union
select '2010-02-01' union
select '2010-03-01' union
select '2010-04-01' union
select '2010-05-01' union
select '2010-06-01' union
select '2010-07-01' union
select '2010-08-01' union
select '2010-09-01' union
select '2010-10-01' union
select '2010-11-01' union
select '2010-12-01'

Answer 1

You can use CTE query for every integer between 1 and 100.

WITH IntegerRangeCTE([i]) AS
(
    SELECT
        1 AS [i]
    UNION ALL
    SELECT
        [i] + 1
    FROM
        IntegerRangeCTE
    WHERE [i] < 100
)
SELECT * FROM IntegerRangeCTE

For dates in interval 2010-01-01 to 2010-12-31 you can use this.

WITH DateRangeCTE([d]) AS
(
    SELECT
        CONVERT(DATETIME, '2010-01-01') AS [d]
    UNION ALL
    SELECT
        DATEADD(m, 1, [d]) AS [d]
    FROM
        DateRangeCTE
    WHERE [d] < DATEADD(m, -1, CONVERT(DATETIME, '2010-12-31'))
)
SELECT * FROM DateRangeCTE

If you get message "The statement terminated. The maximum recursion N has been exhausted before statement completion.", use query hint MAXRECURSION.