Common Table Expression(CTE)

This is a new Enhancement of SQL server 2005(as my knowledge).we can think CTE's as a simple and more powerful alternative to derived Tables.In my case Now I am using CTE's where I used Temporary Tables before and I replaced so many views.

A CTE can be defined as a temporary named result set, which is derived from a simple query and defined within the execution scope of a SELECT, INSERT, UPDATE, or DELETE statement. It is important to note that the scope of a CTE is just the statement in which it is declared. The CTE named result set is not available after the statement in which it is declared and used.

Here I am putting a code sample for CTE witch I have taken from AdventureWorks Database then you all can check this SQL script in your SQL server 2005(if you have installed AdventureWorks DB.)

WITH YearlyOrderAmtCTE(OrderYear, TotalAmount)
AS
(
SELECT YEAR(OrderDate), SUM(OrderQty*UnitPrice)
FROM Sales.SalesOrderHeader AS H JOIN Sales.SalesOrderDetail AS D
ON H.SalesOrderID = D.SalesOrderID
GROUP BY YEAR(OrderDate)
),
SalesTrendCTE(OrderYear, Amount, AmtYearBefore, AmtDifference, DiffPerc)
AS
(
SELECT thisYear.OrderYear, thisYear.TotalAmount,
lastYear.TotalAmount,
thisYear.TotalAmount - lastYear.TotalAmount,
(thisYear.TotalAmount/lastYear.TotalAmount - 1) * 100
FROM YearlyOrderAmtCTE AS thisYear
LEFT OUTER JOIN YearlyOrderAmtCTE AS lastYear
ON thisYear.OrderYear = lastYear.OrderYear + 1
)
SELECT * FROM SalesTrendCTE
GO