Chandradev's Blog

1. CTE is the common table expression. This feature comes in sql server 2005 onward.
2. It is the temporary result set and generally it will be the result set of complex Sub Query.
3. CTE offer the same functionality as View.
4. It is defined with WITH Satement.
5. It improves the readability and ease in maintenance of complex queries and sub query.

CTE Simple Example

WITH CTE1
AS
 (
 SELECT 1 as Col1, 2 as Col2
 )
 
Select * from CTE1

Other Example

WITH CTEExample(EmpName,EmpAddress,DeptName) --Column Name for Temporary Table
AS
(
 Select E.EmpName, E.EmpAddress,D.DeptName from tblEmp E Join tblDept D On  E.DeptId=D.DeptId
)
Select * from CTEExample where CTEExample.DeptName='CS'

When to use CTE ?

1. To simplifying the complex query to simple one like View in sql
2. To maintain more readable code
3. To create recursive query.
4. if you need to reference/join the same data set multiple times you can do so by defining a CTE. Therefore, it can be a form of code re-use.
5. To hold the values as Temp Table.

Point to remember

CTE Life will expire after first select statement.