Data Points: Inline Views Versus Temp Tables

Data Points: Five Ways to Rev up Your SQL Performance "Inline Views Versus Temp Tables
Queries sometimes need to join data to other data that may only be gathered by performing a GROUP BY and then a standard query. For example, if you want to return the information about the five most recently placed orders, you would first need to know which orders they are. This can be retrieved by using a SQL query that returns the orders' IDs. This data could be stored in a temporary table, a common technique, and then joined to the Product table to return the quantity of products sold on those orders:

CREATE TABLE #Temp1 (OrderID INT NOT NULL, _
OrderDate DATETIME NOT NULL)

INSERT INTO #Temp1 (OrderID, OrderDate)
SELECT TOP 5 o.OrderID, o.OrderDate
FROM Orders o ORDER BY o.OrderDate DESC

SELECT p.ProductName, SUM(od.Quantity) AS ProductQuantity
FROM #Temp1 t
INNER JOIN [Order Details] od ON t.OrderID = od.OrderID
INNER JOIN Products p ON od.ProductID = p.ProductID
GROUP BY p.ProductName
ORDER BY p.ProductName

DROP TABLE #Temp1

This batch of SQL creates a temporary table, inserts the data into it, joins other data to it, and drops the temporary table. This is a lot of I/O for this query, which could be rewritten to use an inline view instead of a temporary table. An inline view is simply a query that can be joined to in the FROM clause. So instead of spending a lot of I/O and disk access in tempdb on a temporary table, you could instead use an inline view to get the same result:

SELECT p.ProductName,
SUM(od.Quantity) AS ProductQuantity
FROM (
SELECT TOP 5 o.OrderID, o.OrderDate
FROM Orders o
ORDER BY o.OrderDate DESC
) t
INNER JOIN [Order Details] od ON t.OrderID = od.OrderID
INNER JOIN Products p ON od.ProductID = p.ProductID
GROUP BY
p.ProductName
ORDER BY
p.ProductName

This query is not only more efficient than the previous one, it's shorter. Temporary tables consume a lot of resources. If you only need the data to join to other queries, you might want to try using an inline view to conserve resources."