Execute the following script in Query Editor to demonstrate the use of table variables (@SalesByYear) instead of temporary tables. Table variables are stored in memory and on disk. Table variable operations are not logged. The access time for a table variable may be faster than for a temporary table.

USE AdventureWorks
GO

DECLARE @SalesByYear TABLE
(
OrderYear int NOT NULL,
SalesPerson varchar(60) NOT NULL,
SubTotal money NOT NULL
);

INSERT INTO @SalesByYear
SELECT Year(OrderDate),
FirstName+' '+LastName, SubTotal
FROM Sales.SalesOrderHeader soh
JOIN Person.Contact c
ON c.ContactID = soh.SalesPersonID
WHERE SalesPersonID IS NOT NULL;

SELECT SalesPerson,
[2002]='$'+convert(varchar,isnull([2002],0),1),
[2003]='$'+convert(varchar,isnull([2002],0),1),
[2004]='$'+convert(varchar,isnull([2002],0),1)
FROM @SalesByYear
PIVOT (SUM(SubTotal)
FOR OrderYear IN
([2002],[2003],[2004])) as SalesPivot
ORDER BY SalesPerson

GO