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