SQL Server: Global Temporary tables

In this post explains how to use the GLOBAL TEMPORARY TABLES in SQL Server (Transact-SQL) with syntax and examples.

Description

GLOBAL TEMPORARY TABLES in SQL Server (Transact-SQL) are tables that are created distinct within the SQL Server sessions.

Syntax

The syntax for CREATE GLOBAL TEMPORARY TABLE in SQL Server (Transact-SQL) is:

CREATE TABLE ##table_name
( 
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
);

Parameters or Arguments

table_name

The name of the global temporary table that you wish to create. The name of the global temporary table starts with ## characters.

column1, column2

The columns that you wish to create in the global temporary table. Each column must have a datatype. The column should either be defined as NULL or NOT NULL and if this value is left blank, the database assumes NULL as the default.

Note

• The name of GLOBAL TEMPORARY TABLES are prefixed with ## characters (ie: ##employees).

Example

Let's look at an example of how to create a GLOBAL TEMPORARY TABLE in SQL Server (Transact-SQL).

For example:

CREATE TABLE ##employees
( employee_id INT PRIMARY KEY,
  last_name VARCHAR(50) NOT NULL,
  first_name VARCHAR(50),
  salary MONEY
);

This example would create a GLOBAL TEMPORARY TABLE called ##employees in SQL Server which has 4 columns.

• The first column is called employee which is created as an INT datatype and can not contain NULL values.
• The second column is called last_name which is a VARCHAR datatype (50 maximum characters in length) and also can not contain NULL values.
• The third column is called first_name which is a VARCHAR datatype but can contain NULL values.
• The fourth column is called salary which is a MONEY datatype which can contain NULL values.
• The primary key for the #employees table is the employee_id column.

This ##employees table is stored in tempdb and SQL Server will automatically delete this table when all users referencing the table have disconnected from the SQL Server session.