SQL Server 2005: Common Table Expressions (CTE)

Microsoft SQL Server 2005 offers a new feature i.e Common Table Expression (CTE). Which is more readable form of the derived table that can be declared once and referenced multiple times in a query
.CTE can be used in select,insert,delete,update
.CTE can be defined recursively
General Syntax
WITH cte_alias (column_aliases)
AS
(
cte_query_definition --initialization
UNION ALL
cte_query_definition2 --recursive execution
)
SELECT * FROM cte_alias
Example 1:
"
with TestCTE(testVar)
as
(
select testVar = convert(varchar(300),'Nitil')
union all
select testVar + 'a' from TestCTE where len(testVar) < 10
)
select testVar from TestCTE
order by testVar "


Result
testVar
Nitil
Nitila
Nitilaa
Nitilaaa
Nitilaaaa
Nitilaaaaa

Example 2:
"
with TestCTE(testVar)
as
(
select testVar = convert(varchar(8000),'Nitil')
union all
select testVar + 'a' from TestCTE where len(testVar) < 10
union all
select testVar + ' Have a Nice Day' from TestCTE where len(testVar) < 10

)
select testVar from TestCTE
order by len(testVar),testVar"
Result
testVar
Nitil
Nitila
Nitilaa
Nitilaaa
Nitilaaaa
Nitilaaaaa
Nitil Have a Nice Day
Nitila Have a Nice Day
Nitilaa Have a Nice Day
Nitilaaa Have a Nice Day
Nitilaaaa Have a Nice Day

Simple CTE’s
"
with CTETable( column1,column2) as
(
select id,Employee_Name from Employee
)
select * from CTETable "
Result:
column1 column2
1 EmpName1
2 EmpName2

Multiple CTE
"
with testing(column1Id,column2Name) as
(
select id,username from ocean_user
),
rectesting(nam,sal) as
(
select t.column2Name,e.sal from Employee e,testing t
where e.id=t.emp_id and sal > 10000
)
select * from rectesting "