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.
GLOBAL TEMPORARY TABLES in SQL Server (Transact-SQL) are tables that are created distinct within the SQL Server sessions.
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
- 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.
- The name of GLOBAL TEMPORARY TABLES are prefixed with ## characters (ie: ##employees).
Let’s look at an example of how to create a GLOBAL TEMPORARY TABLE in SQL Server (Transact-SQL).
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.