Ramp up the output row count to about a quarter million and blackhole the output (an attempt to eliminate io from the execution time), and a tally table version runs about 30 times faster than the recursive CTE script. rCTE's are expensive and I wouldn't recommend using a rCTE for something so simple as this - it's a sledgehammer on a nut.

What might come as a surprise is how fast it runs - around 2 seconds to generate 220,000 rows. That's not really RBAR figures and compared to some data generator times it's pretty darned good. Not as good as the inline tally table though:

-- Alter the seed table to support a larger data set

/*

ALTER TABLE dbo.abc ALTER COLUMN Month_Count INT NULL

ALTER TABLE dbo.abc ALTER COLUMN Date_Field DATETIME NULL

GO

UPDATE dbo.abc SET Month_Count = Month_Count*5000

GO

*/

------------ Inline Tally version ------------------

-- black hole variables:

DECLARE @SeqNo SMALLINT, @Date_Field DATETIME, @Payment DECIMAL (10,2)

DECLARE @StartTime DATETIME;

-- time store

SET @StartTime = GETDATE()

;WITH E1(n) AS (

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1), -- 10 rows

E2(n) AS (SELECT 1 FROM E1 a, E1 b),-- 10x10 rows

E3(n) AS (SELECT 1 FROM E2 a, E2 b, E2 c)-- 100x100x100 rows

SELECT

@SeqNo = SeqNo,

@Date_Field = DATEADD(MONTH,x.n,Date_Field),

@Payment = Payment

FROM dbo.abc

CROSS APPLY(SELECT TOP(Month_Count) n = -1+ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E3) x

SELECT DATEDIFF(MS,@StartTime,GETDATE())

PRINT '-------------------------------------------------------------------------'

-------------- rCTE version ------------------------------

SET @StartTime = GETDATE()

;with CTE_Base (SeqNo, Date_Field, Month_Count, Payment, Begin_Date, End_Date, Frequency)

as

(select SeqNo, Date_Field, Month_Count, Payment, Date_Field, dateadd(mm, Month_Count-1, Date_Field), 1 from dbo.abc

union all

select SeqNo, dateadd(mm, Frequency, Date_Field), Month_Count, Payment, Begin_Date, End_Date, Frequency

from CTE_Base

where dateadd(mm, Frequency, Date_Field) between Begin_Date and End_Date)

select

@SeqNo = SeqNo,

@Date_Field = Date_Field,

@Payment = Payment

from CTE_Base

where Date_Field between Begin_Date and End_Date

order by SeqNo, Date_Field

OPTION(MAXRECURSION 0)

SELECT DATEDIFF(MS,@StartTime,GETDATE())

PRINT '-------------------------------------------------------------------------'