Sql server® 2012 t-sql fundamentals

Local Temporary Tables

You create a local temporary table by naming it with a single number sign as a prefix, such as #T1. All 

three kinds of temporary tables are created in the tempdb database. 

A local temporary table is visible only to the session that created it, in the creating level and all 

inner levels in the call stack (inner procedures, functions, triggers, and dynamic batches). A local 

temporary table is destroyed automatically by SQL Server when the creating level in the call stack 

goes out of scope. For example, suppose that a stored procedure called Proc1 calls a procedure called 

Proc2, which in turn calls a procedure called Proc3, which in turn calls a procedure called Proc4. Proc2 

creates a temporary table called #T1 before calling Proc3. The table #T1 is visible to Proc2, Proc3, 

and Proc4 but not to Proc1, and is destroyed automatically by SQL Server when Proc2 finishes. If the 

temporary table is created in an ad-hoc batch in the outermost nesting level of the session (in other 

words, when the value of the @@NESTLEVEL function is 0), it is visible to all subsequent batches as 

well and is destroyed by SQL Server automatically only when the creating session disconnects.

You might wonder how SQL Server prevents name conflicts when two sessions create local tem-

porary tables with the same name. SQL Server internally adds a suffix to the table name that makes 

it unique in tempdb. As a developer, you shouldn’t care—you refer to the table using the name you 

provided without the internal suffix, and only your session has access to your table.

One obvious scenario for which local temporary tables are useful is when you have a process that 

needs to store intermediate results temporarily—such as during a loop—and later query the data. 

Another scenario is when you need to access the result of some expensive processing multiple 

times. For example, suppose that you need to join the Sales.Orders and Sales.OrderDetails tables, 

aggregate order quantities by order year, and join two instances of the aggregated data to compare 

each year’s total quantity with the previous year. The Orders and OrderDetails tables in the sample 

database are very small, but in real-life situations such tables can have millions of rows. One option is 

to use table expressions, but remember that table expressions are virtual. The expensive work involv-

ing scanning all the data, joining the Orders and OrderDetails tables, and aggregating the data would 

have to happen twice with table expressions. Instead, it makes sense to do all the expensive work only 

once—storing the result in a local temporary table—and then join two instances of the temporary 

table, especially because the result of the expensive work is a very tiny set with only one row per each 

order year.

www.it-ebooks.info

---

354  

Microsoft SQL Server 2012 T-SQL Fundamentals

The following code illustrates this scenario using a local temporary table.

IF OBJECT_ID('tempdb.dbo.#MyOrderTotalsByYear') IS NOT NULL 

  DROP TABLE dbo.#MyOrderTotalsByYear; 

GO 

 

CREATE TABLE #MyOrderTotalsByYear 

( 

  orderyear INT NOT NULL PRIMARY KEY, 

  qty       INT NOT NULL 

); 

 

INSERT INTO #MyOrderTotalsByYear(orderyear, qty) 

  SELECT 

    YEAR(O.orderdate) AS orderyear, 

    SUM(OD.qty) AS qty 

  FROM Sales.Orders AS O 

    JOIN Sales.OrderDetails AS OD 

      ON OD.orderid = O.orderid 

  GROUP BY YEAR(orderdate); 

 

SELECT Cur.orderyear, Cur.qty AS curyearqty, Prv.qty AS prvyearqty 

FROM dbo.#MyOrderTotalsByYear AS Cur 

  LEFT OUTER JOIN dbo.#MyOrderTotalsByYear AS Prv 

    ON Cur.orderyear = Prv.orderyear + 1;

This code produces the following output.

orderyear   curyearqty  prvyearqty 

----------- ----------- ----------- 

2007        25489       9581 

2008        16247       25489 

2006        9581        NULL

To verify that the local temporary table is visible only to the creating session, try accessing it from 

another session.

SELECT orderyear, qty FROM dbo.#MyOrderTotalsByYear;

You get the following error.

Msg 208, Level 16, State 0, Line 1 

Invalid object name '#MyOrderTotalsByYear'.

When you’re done, go back to the original session and drop the temporary table.

IF OBJECT_ID('tempdb.dbo.#MyOrderTotalsByYear') IS NOT NULL 

  DROP TABLE dbo.#MyOrderTotalsByYear;

It is generally recommended that you clean up resources as soon as you are done working with them.

www.it-ebooks.info

---