Sql server® 2012 t-sql fundamentals

 

CHAPTER 10

 

Programmable Objects

 

 355

Global Temporary Tables

note

  At the date of this writing, global temporary tables are not supported by Windows 

Azure SQL Database, so if you want to run the code samples from this section, you will 

need to connect to an on-premises SQL Server instance. 

When you create a global temporary table, it is visible to all other sessions. Global temporary tables 

are destroyed automatically by SQL Server when the creating session disconnects and there are no 

active references to the table. You create a global temporary table by naming it with two number 

signs as a prefix, such as ##T1.

Global temporary tables are useful when you want to share temporary data with everyone. No 

special permissions are required, and everyone has full DDL and DML access. Of course, the fact that 

everyone has full access means that anyone can change or even drop the table, so consider the alter-

natives carefully.

For example, the following code creates a global temporary table called ##Globals with columns 

called id and val. 

CREATE TABLE dbo.##Globals 

( 

  id  sysname     NOT NULL PRIMARY KEY, 

  val SQL_VARIANT NOT NULL 

);

This table in this example is intended to mimic global variables, which are not supported by SQL 

Server. The id column is of a sysname data type (the type that SQL Server uses internally to represent 

identifiers), and the val column is of a SQL_VARIANT data type (a generic type that can store within it 

a value of almost any base type). 

Anyone can insert rows into the table. For example, run the following code to insert a row repre-

senting a variable called i and initialize it with the integer value 10.

INSERT INTO dbo.##Globals(id, val) VALUES(N'i', CAST(10 AS INT));

Anyone can modify and retrieve data from the table. For example, run the following code from any 

session to query the current value of the variable i.

SELECT val FROM dbo.##Globals WHERE id = N'i';

This code returns the following output.

val 

----------- 

10

www.it-ebooks.info

---