Working With Temporary Views Using Common Table Expression in SQL Server 2005

When working with SQL 2005, there are times when you need to perform several aggregate functions, or to define a view that needs to be stored only during the execution of a particular query. A Common Table Expression (CTE) does just that; it helps in the definition and manipulation of query related results that need temporary storage. Its scope is limited to the statement that defines it. CTEs provide an easy way of writing and reviewing queries. Another unique and defining element about CTE is that it can self-reference, meaning it can refer to itself as many times as required in the same query. Microsoft developed the Common Table Expression for SQL Server 2005 based on the ANSI SQL-99 standard. Before its introduction, SQL 2000 users were using derived and temporary tables.

Types of Common Table Expression

CTEs can be recursive and non-recursive. In its non-recursive form a CTE can serve as a substitute for derived tables or a view, and can be used in place of user-defined routines. It provides a convenient tool for creating queries by building tables as and when they are required within nested SELECT statements. This simplifies query building by allowing them to be developed in separate logical blocks.

In a recursive CTE, the original CTE is executed repetitively in order to arrive at different subsets of data that together form the complete result set. It is best used in queries that return hierarchical data results like reporting relationships within an organization. A good example would be employees in an organizational chart or products that have subcomponents. You can think of it as a combination of a hybrid derived table and a declared temporary table.

Creating a Common Table Expression

The syntax for creating a CTE consists of the WITH statement followed by the expression name that will identify the CTE and a query definition. If the query definition provides for separate names for the columns that will store the results then a column list is provided immediately after the expression name of the CTE. The basic syntax is:

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

In the following example, a Common Table Expression "MyCTE" is being defined that has two resulting columns LP and SP. The statement following AS provides the query definition by populating the two column names. MyCTE can be run by using the SELECT statement.

USE AdventureWorks
GO

WITH MyCTE (LP, SP) AS
  (
   SELECT LP, LP * .95 FROM itemlist.item
  )
  SELECT * FROM My_CTE
GO