Common Table Expression (CTE) in SQL SERVER..

I was really confused while trying Common Table Expression at first, that i even started ignoring it, but the functionality is really helpful if you spent some time..

CTE is a temporary result set that u can refer back to a query, which the query can use to do further operation.

Basically there r 2 types of CTE supported by Sql Server, recursive and non-recursive.

Let me try to explain u both.

[WITH [,...]]

::=cte_name [(column_name [,...])]

AS (cte_query)

CTE Syntax :

CTE starts with a WITH clause, - we can name it as a holder.

What follows the WITH clause is the name for the CTE - mine would be "nonrecursive ".

Now we want to specify the columns that the CTE will return, which should be within the resultset of the inner query (i.e the query definition of the CTE).

WITH
nonrecursive (EmpId, NetSalary)
AS
(
SELECT EmpId, SUM(Salary)
FROM EmpSalary
WHERE datepart(Yy,CreatedDate) = 2010
GROUP BY EmpId
)
SELECT
e.EmpName,
e.City + ', ' + e.State + ', ' + e.Pincode AS Address,
ct.NetSalary
FROM Employee e
INNER JOIN nonrecursive ct
ON e.EmpId = ct.EmpId
WHERE ct.NetSalary > 50000
ORDER BY ct.NetSalary DESC


As discussed earlier ,CTE Is like temporary storage, so any lookup that we do
should be done simultaneously ( i.e In the above example if you run the
"WITH" clause and select separately, then it will throw you with errors ).


So what does the above example do.

1. The query definition inside the WITH clause selects you each employee and sum of his salary for the year 2010.

2. The outer query selects the employee details with netsalary with more than 50000.

Now lets look at recursive query..