SQL Server: LOCAL TEMPORARY TABLES

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

Description

LOCAL TEMPORARY TABLES are distinct within modules and embedded SQL programs within SQL Server sessions. LOCAL TEMPORARY TABLES are stored in tempdb and SQL Server automatically deletes these tables when they are no longer used.

Syntax

The syntax to create a LOCAL 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 local temporary table that you wish to create. The name of the local temporary table starts with the # character.

column1, column2

The columns that you wish to create in the local 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 LOCAL TEMPORARY TABLES are prefixed with the # character (ie: #employees).

Example

Let's look at an example of how to create a LOCAL 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 LOCAL 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 the SQL Server session no longer requires it.