SQL Server: LOCAL TEMPORARY TABLES
This SQL Server tutorial explains how to use the LOCAL TEMPORARY TABLES in SQL Server (Transact-SQL) with syntax and examples.
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.
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
- 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.
- The name of LOCAL TEMPORARY TABLES are prefixed with the # character (ie: #employees).
Let’s look at an example of how to create a LOCAL 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 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.