What is the CTE in SQL Server ?

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

 SELECT 1 as Col1, 2 as Col2
Select * from CTE1

Other Example

WITH CTEExample(EmpName,EmpAddress,DeptName) --Column Name for CTE
 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

1. CTE Life will expire after first select statement.
2. In CTE, We can write only one Select query.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.