How to yield recursion in query? What is the use of "With" ? How it works internaly ?

Question

Why this query is completed with error ?

;with tempData as
 (
        select 32 as col1, char(32) as col2
        union all
        select col1+1, char(col1+1) from tempData
 )
select * from tempData

Answer 1

You have an infinite loop: where should it end?

Answer 2

Recursion needs a terminating condition. For example

;with tempData as ( 
select 32 as col1, char(32) as col2 
union all 
select col1+1, char(col1+1) from tempData 
where col1 < 255
) 
select * from tempData
option (maxrecursion 223)

With regards to the question in the title about how it works internally see this answer.