SQL Server: Global Temporary tables
This
SQL Server tutorial 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
#EmpDetails
( employee_id INT PRIMARY KEY,
last_name VARCHAR(200) NOT NULL,
first_name VARCHAR(200),
);
This example would create a
GLOBAL TEMPORARY TABLE called ## EmpDetails in SQL
Server which has 4 columns.
- The first column is called employee_id which is created as an INT
datatype and cannot 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 primary key for the #employees table is the employee_id column.
This
## EmpDetails 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.
