Common Table Expressions (CTE) are a useful new feature that allows you to define a virtual view. They are useful with aggregates as well as the ROW_NUMBER() and RANK() functions. So basically, you can create a temporary view inside a stored procedure, perform some operation with it, and then it goes away automatically. I have used this many times when I need to insert the average of something into a new table. The most common way to use a CTE is with a SELECT statement.
WITH MyCTE
AS (SELECT Id, Column1, AVG(Column2) as ColumnAverage
FROM MyTable WHERE SomeColumn = @SomeValue)
You can use just about any SELECT statement you want although there is some wierdness when you do stuff with groups. Once the CTE is built you can query from it like the following.
SELECT ColumnAverage FROM MyCTE
WHERE SomeOtherColumn = @SomeOtherColumn ORDER BY Id DESC
Or maybe you need to update some table...
UPDATE MyOtherTable
SET MyOtherTable.ColumnAverage = MyCTE.ColumnAverage
FROM MyOtherTable
INNER JOIN MyCTE
ON MyOtherTable.ColumnAverage = MyCTE.ColumnAverage
Prior to SQL Server 2005, the statement like the above would have been a pain in the ass requiring temp tables and what not. Now, it is pretty simple.
A couple of other things to remember with CTEs. First, any preceding statement to the declaration of a CTE must end with a semi-colon (;). Secondly, if not in a stored procedure, the statement querying or using the defined CTE must occur immediately after the definition (i.e.: if you are testing in SQL Server Management Studio).
Read the complete post at http://www.dotnettipoftheday.com/blog.aspx?id=318