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


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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: