-- For demonstration purposes only
USE tempdb;
GO
-- Conditional object drops
IF  OBJECT_ID(N'dbo.MonthYearList') 
    IS NOT NULL 
    DROP FUNCTION dbo.MonthYearList;
GO
IF  OBJECT_ID(N'dbo.Numbers') 
    IS NOT NULL 
    DROP FUNCTION dbo.Numbers;
GO
-- An in-line function to create a numbers table on the fly
CREATE  FUNCTION dbo.Numbers
        (@N BIGINT)
RETURNS TABLE
WITH    SCHEMABINDING
AS      RETURN
        WITH    
        N1 AS (SELECT N = 1 UNION ALL SELECT 1),
        N2 AS (SELECT N = 1 FROM N1 T, N1),
        N3 AS (SELECT N = 1 FROM N2 T, N2),
        N4 AS (SELECT N = 1 FROM N3 T, N3),
        N5 AS (SELECT N = 1 FROM N4 T, N4),
        N6 AS (SELECT N = 1 FROM N5 T, N5),
        NM AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS N FROM N6)
        SELECT  N
        FROM    NM
        WHERE   @N >= 0
        AND     N <= @N
GO
-- An in-line function wrapping the set-based implementation
CREATE  FUNCTION dbo.MonthYearList
        (@From DATETIME)
RETURNS TABLE
WITH    SCHEMABINDING
AS      RETURN
        SELECT  the_year = YEAR(This.month_start),
                the_month = MONTH(This.month_start)
        FROM    dbo.Numbers (DATEDIFF(MONTH, @From, CURRENT_TIMESTAMP) + 1)
        CROSS
        APPLY   (SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0)) Base (month_start)
        CROSS
        APPLY   (SELECT DATEADD(MONTH, 1 - N, Base.month_start)) This (month_start)
        WHERE   This.month_start BETWEEN @From AND CURRENT_TIMESTAMP;
GO
-- Collect performance statistics
SET STATISTICS IO, TIME ON;

-- The RBAR method
WITH mycte AS (SELECT cast ('1753-01-01' AS DATETIME) DateValue UNION ALL SELECT DateValue + 1 FROM mycte WHERE DateValue +
1 < getdate())
SELECT distinct YEAR(DateValue) MyYear,MONTH(DateValue) MyMonth
from mycte OPTION ( MAXRECURSION 0 )

-- Set based
SELECT  *
FROM    dbo.MonthYearList ('17530101');
        
-- Stop recording statistics
SET STATISTICS IO, TIME OFF;
GO
-- Tidy up
DROP FUNCTION dbo.MonthYearList;
DROP FUNCTION dbo.Numbers;