Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted May 6, 2005

Common Table Expressions (CTE) on SQL 2005

By Don Schlichting

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM