CTE

A CTE allows you to define a temporary named result set that available temporarily in the execution scope of a statement such as SELECT, INSERT, UPDATE, DELETE, or MERGE.select statement

 
That immediately follow the cte.
 
syntax of cte

with CTE(column_1,column_2,.....)
as
query

select/update/delete/insert

with cte as (

select * from employee where salary>45000

)

select * from cte

example 2:

with cte as (

select * from employee e join department d on e.dept=d.dept_name where salary>45000

)

select * from cte

If we update the CTE it will update the base table

 with CTE(column_1,column_2,.....)
as
select * from
update
 
*** if cte is based on multiple table and if the update statement affect

more than one table,then update is not allowed.

but if its affect one table then it will be success(but not always) 
Using multiple SQL Server CTE in a single query example

with emp as (

select * from employee where salary>45000 

),

dept as (

select * from department

)

select * from emp join dept on emp.dept=dept.dept_name

Recursive CTE:

A recursive common table expression (CTE) is a CTE that references itself. By doing so, the CTE repeatedly executes, returns subsets of data, until it returns the complete result set.

In general, a recursive CTE has three parts:

1. An initial query that returns the base result set of the CTE. The initial query is called an anchor member.
2. A recursive query that references the common table expression, therefore, it is called the recursive member. The recursive member is union-ed with the anchor member using the UNION ALL operator.
3. A termination condition specified in the recursive member that terminates the execution of the recursive member.

with cte as

(

select name,manager from employee where manager is null

union all

select e.name,e.manager from employee e

join cte  on cte.name=e.manager

)

select * from cte

the anchor member gets the top manager and the recursive query returns subordinates of the top managers and subordinates of the top manager, and so on.