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 compared with 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 actually merges the outer query and 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 showed and will continue to show many 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, while views and inline UDFs are available globally to users that have permissions to access them. I’ll discuss views and inline UDFs later in this book in Chapter 5 and Chapter 6. For details on derived tables and CTEs, please refer to Inside T-SQL Querying.

As an example of using a table expression to solve a problem, suppose you want to return from the Orders table in the Northwind database, the row with the highest OrderID for each employee. Here’s a solution that uses a derived table, generating the output shown in Table 2-3:

USE Northwind;

SELECT O.OrderID, O.EmployeeID, O.CustomerID, O.OrderDate
FROM dbo.Orders AS O
  JOIN (SELECT EmployeeID, MAX(OrderID) AS MaxOid
        FROM dbo.Orders
        GROUP BY EmployeeID) AS D
    ON O.OrderID = D.MaxOid;