Common Table Expressions (CTE) on SQL 2005

This article will introduce Common Table Expressions (CTE)
on SQL 2005, and compare them with related SQL 2000 methods such as Derived and
Temporary Tables.

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.