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
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.