Sql server® 2012 t-sql fundamentals

356  

Microsoft SQL Server 2012 T-SQL Fundamentals

note

  Keep in mind that as soon as the session that created the global temporary table dis-

connects and there are no active references to the table, SQL Server automatically destroys 

the table.

If you want a global temporary table to be created every time SQL Server starts, and you don’t 

want SQL Server to try to destroy it automatically, you need to create the table from a stored pro-

cedure that is marked as a startup procedure (for details, see “sp_procoption” in SQL Server Books 

Online at the following URL: http://msdn.microsoft.com/en-us/library/ms181720.aspx).

Run the following code from any session to explicitly destroy the global temporary table.

DROP TABLE dbo.##Globals;

Table Variables

Table variables are similar to local temporary tables in some ways and different in others. You declare 

table variables much like you declare other variables, by using the DECLARE statement.

As with local temporary tables, table variables have a physical presence as a table in the tempdb 

database, contrary to the common misconception that they exist only in memory. Like local tempo-

rary tables, table variables are visible only to the creating session, but they have a more limited scope: 

only the current batch. Table variables are visible neither to inner batches in the call stack nor to 

subsequent batches in the session. 

If an explicit transaction is rolled back, changes made to temporary tables in that transaction are 

rolled back as well; however, changes made to table variables by statements that completed in the 

transaction aren’t rolled back. Only changes made by the active statement that failed or that was 

terminated before completion are undone. 

Temporary tables and table variables also have optimization differences, but those are outside the 

scope of this book. For now, I’ll just say that in terms of performance, usually it makes more sense 

to use table variables with very small volumes of data (only a few rows) and to use local temporary 

tables otherwise.

For example, the following code uses a table variable instead of a local temporary table to com-

pare total order quantities of each order year with the year before.

DECLARE @MyOrderTotalsByYear TABLE 

( 

  orderyear INT NOT NULL PRIMARY KEY, 

  qty       INT NOT NULL 

); 

 

www.it-ebooks.info

---