Common Table Expressions in SQL Server 2005 (CTE)

Common Table Expressions, CTE in short, is a new feature in SQL Server 2005. CTE is a temporary result set and is defined as part of a SELECT, INSERT, UPDATE, DELETE and CREATE VIEW statements. A very simple usage of a CTE is given below.

WITH MyCTE( ListPrice, SellPrice) AS ( SELECT ListPrice, ListPrice * .95 FROM Production.Product ) SELECT * FROM MyCTE WHERE SellPrice > 100

A CTE definition requires three things, viz, a name for the CTE (MyCTE in the above example), an optional list of columns (ListPrice and SellPrice) and the query following the AS keyword.

Using CTE could improve readability when used in complex queries involving several tables. It would be a good replacement in cases where you are using a temporary table just once after creation. The advantages of using CTE are given below.

• Create a recursive query.
• Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
• Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
• Reference the resulting table multiple times in the same statement.

To learn more about the capabilities and limitations of CTE, visit the MSDN article MSDN site.