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

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

Other Example

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

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

Advertisements

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: