Introduced in SQL Server 2005, the common table expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. You can also use a CTE in a CREATE VIEW statement, as part of the view’s SELECT query. In addition, as of SQL Server 2008, you can add a CTE to the new MERGE statement.
SQL Server supports two types of CTEs—recursive and nonrecursive. In this article, it explains how to create both types. The examples provided are based on a local instance of SQL Server 2008 and retrieve data from the AdventureWorks2008 sample database.
Common Table Expression CTE