Common Table Expressions (CTE) on SQL 2005 By Don Schlichting

Introduction

New in SQL 2005 are Common Table Expressions, CTE for short. A Common Table Expression is an expression that returns a temporary result set from inside a statement. This result set is similar to a hybrid Derived Table meets declared Temporary Table. The CTE contains elements similar to both. Some of the most frequent uses of a Common Table Expression include creating tables on the fly inside a nested select, and doing recursive queries. Common Table Expressions can be used for both selects and DML statements. The natural question is, if we have been using TSQL for this long without Common Table Expressions, why start using them now? There are several benefits to learning CTEs. Although new to SQL Server, Common Table Expressions are part of ANSI SQL 99, or SQL3. Therefore, if ANSI is important to you, this is a step closer. Best of all, Common Table Expressions provide a powerful way of doing recursive and nested queries in a syntax that is usually easier to code and review than other methods.

CTE

Below is very simple Common Table Expression example. All the CTE examples in this article were created on SQL 2005 Beta 2 Developer Edition. The example CTE will be used to return the list price of products, and our sell price, which is five percent below list. This first example is very simple, and could be replaced by a single select statement, but it will demonstrate some key CTE points. The examples will get more advanced as the article progresses.

USE AdventureWorks
GO

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

SELECT *
FROM MyCTE

GO

The database, Adventure Works, is now the SQL 2005 default sample database. Gone are the days of Northwind. On the beta version used for this article, Adventure Works did not install by default. Instead, during install, click Advanced then select sample databases.
The Common Table Expression is created using the WITH statement followed by the CTE name. Immediately trailing is a column list, in our case, we are returning two columns, ListPrice, and SellPrice. After the AS, the statement used to populate the two returning columns begins. The CTE is then followed by a select calling it.
The BOL format of a Common Table Expression is listed below;

[ WITH <common_table_expression> [ ,...n ] ]

<common_table_expression>::=
    expression_name [ ( column_name [ ,...n ] ) ]
    AS
    ( CTE_query_definition )

Temporary Tables

This small example displays several interesting concepts. The CTE is called by name from the SELECT * statement, similar to a Temporary Table. However, if a Temporary Table were used, it would first have to be created, and then populated;

CREATE TABLE #MyCTE
(
  ListPrice money,
  SellPrice money
)

INSERT INTO #MyCTE
 (ListPrice, SellPrice)
 SELECT ListPrice, ListPrice * .95
 FROM Production.Product   

However, a Temporary Table could be called over and over again from with in a statement. A Common Table Expression must be called immediately after stating it. Therefore, in this example, the call to the CTE will fail.

USE AdventureWorks
GO

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

SELECT *
FROM Production.Location

SELECT *
FROM MyCTE

GO

The call to MyCTE will fail with the following error:

Msg 208, Level 16, State 1, Line 14
Invalid object name 'MyCTE'.

The CTE itself has some syntactical restrictions. Compute, Order By (without a TOP), INTO, Option, FOR XML, and FOR BROWSE are all not allowed.


http://www.databasejournal.com/features/mssql/article.php/3502676/Common-Table-Expressions-CTE-on-SQL-2005.htm