CTE che contiene una colonna contatore generata

declare @limit int
set @limit = 100

;with MyCte AS
    (select   MyCounter = 1
     UNION ALL
     SELECT   MyCounter + 1
     FROM     MyCte
     where    MyCounter < @limit )
    
select MyCounter
from   MyCte
option (maxrecursion 0)