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

CTEs and Temporary Tables

A CTE can be created using the WITH statement with the CTE name following it. You will find that temporary tables aren't as convenient. For instance, unlike CTEs, temporary tables can be populated only after they have been created and not otherwise. Study the following syntax to note the difference:

CREATE TABLE #My_CTE
(
  LP money, SP money
)
   INSERT INTO #My_CTE
   (LP, SP)
        SELECT LP, LP * .95 FROM itemlist.item

Temporary tables as used in SQL 2000, allow them to be called repeatedly from within a statement, whereas in a Common Table Expression, it can be called immediately after stating it. Hence, if you write syntax similar to the following example, there won't be any response to the CTE you call.

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

Both a CTE and a temporary table can be called by name using the SELECT * statement.

CTEs and Derived Tables

Derived tables in SQL 2000 and CTEs in SQL 2005 are very similar. Derived tables serve in simple queries; however, they suffer from two drawbacks: they can be used only once, and you cannot refer to it by name. CTEs score over derived tables when it comes to complex statements. In fact, CTEs can efficiently handle simple queries too, since they can be rewritten as derived tables as shown below:

  SELECT * FROM
  (
     SELECT LP, (LP * .95) AS SP
        FROM itemlist.item
   )
  MyDerivedTable

Recursive Queries

A recursive query used in SQL Server 2005 refers to a recursive CTE. It allows you to set up arbitrary levels of queries on data that have a tree like structure (hierarchical), for instance, the employee reporting structure in an organization — something that was not possible in SQL 2000. You can either set the number of levels of recursion you want or leave them without any limit depending upon the query required.

Let us study the following example of reporting an organizational hierarchy where a initial subquery is set up to return only those records (employees) that report to the top management (represented by Mng_ID = null). If we limit the number of levels of recursion here, we will be excluding those employees who fall outside the defined chain of command. If we set no limit, no employee is excluded but there is a risk of an infinite recursion occurring if any employee happens to be reporting directly or indirectly to himself, like a director who is also an employee. In such cases, you can join the table to itself once for each level.

A recursive subquery is then set up by using the CTE name "DictRep" to append additional rows to the result set. The two are then connected by using the operator UNION ALL. The first or the initial subquery is nonrecursive and is processed first while the recursive query refers to the rows that were added in the previous cycle. Whenever iteration generates no new rows, recursion comes to a halt. Running the following syntax will display the result set that shows the reporting levels in the organization.

USE AdventureWorks ;
GO
WITH DictRep(Log_ID, Mng_ID, Emp_ID) AS
(
    SELECT Log_ID, Mng_ID, Emp_ID
    FROM HRs.Emp
    WHERE Mng_ID IS NULL
    UNION ALL
    SELECT e.Log_ID, e.Mng_ID, e.Emp_ID
            FROM HRs.Emp e
    INNER JOIN DictRep d
    ON e.Mng_ID = d.Emp_ID
)
SELECT * FROM DictRep ;
GO

The result set would be:

Recursive CTEs need an anchor member definition and a UNION ALL statement to generate appropriate results.

For all those who have so far been working with temporary tables and derived tables alone, Common Expression Tables are a good option to explore. Unlike recursive routines used in other languages, a recursive CTE can return multiple rows instead of one scalar value. It can handle complex queries and has a syntax that is easy to understand, read and write.