Temporary Tables and Table Variables in Microsoft SQL Server 2008 T-SQL Programming

Table Expressions

In this chapter’s opening paragraphs, I mentioned that there might be cases in which you need “logical” temporary tables—that is, only virtual materialization of interim sets, as opposed to physical materialization in temporary tables and table variables. Table expressions give you this capability. These include derived tables, CTEs, views, and inline table-valued UDFs. Here I’ll point out the scenarios in which these are preferable to other temporary objects and provide an example.

You should use table expressions in cases where you need a temporary object mainly for simplification—for example, when developing a solution in a modular approach, a step at a time. Also, use table expressions when you need to access the temporary object only once or a very small number of times and you don’t need to index interim result sets. SQL Server doesn’t physically materialize a table expression. The optimizer merges the outer query with the inner one, and it generates one plan for the query accessing the underlying tables directly. So I’m mainly talking about simplification, and I show such examples throughout the book. But even beyond simplification, in some cases you will be able to improve performance of solutions by using table expressions. There might be cases where the optimizer will generate a better plan for your query compared to alternative queries.

In terms of scope and visibility, derived tables and CTEs are available only to the current statement, whereas views and inline UDFs are available globally to users that have permissions to access them.

As an example of using a table expression to solve a problem, suppose you want to return from the Sales.Orders table in the InsideTSQL2008 database, the row with the highest orderid for each employee. Here’s a solution that uses a CTE:

USE InsideTSQL2008;

WITH EmpMax AS
(
  SELECT empid, MAX(orderid) AS maxoid
  FROM Sales.Orders
  GROUP BY empid
)
SELECT O.orderid, O.empid, O.custid, O.orderdate
FROM Sales.Orders AS O
  JOIN EmpMax AS EM
    ON O.orderid = EM.maxoid;

This generates the following output:

orderid     empid       custid      orderdate
----------- ----------- ----------- -----------------------
11077       1           65          2008-05-06 00:00:00.000
11073       2           58          2008-05-05 00:00:00.000
11063       3           37          2008-04-30 00:00:00.000
11076       4           9           2008-05-06 00:00:00.000
11043       5           74          2008-04-22 00:00:00.000
11045       6           10          2008-04-23 00:00:00.000
11074       7           73          2008-05-06 00:00:00.000
11075       8           68          2008-05-06 00:00:00.000
11058       9           6           2008-04-29 00:00:00.000