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.

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 
    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