|
Execute the following Microsoft SQL Server Transact-SQL (T-SQL) script in Management Studio (SSMS) Query Editor, SQLCMD or other client software
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
|
