Temporary Tables

SQL Server supports two types of temporary tables: local and global. A local temporary table is visible only to the connection that created it. A global temporary table is available to all connections. Local temporary tables are automatically dropped at the end of the current session. Global temporary tables are dropped at the end of the last session using the table (normally, this is when the session that created the table ends).

Temporary tables named with # and ## can be created by any user. Once the temporary table is created, the owner of the local table is the only one who can access that table. Permissions cannot be granted on local temporary tables. Once a global temporary table is created, all users can access it; permissions cannot be explicitly revoked. Explicitly creating a temporary table in tempdb (named without a pound sign) can be performed only by those with explicit CREATE TABLE permission in the tempdb database. Permission can be granted to and revoked from these tables.

Because temporary tables are not generally maintained for long (they are automatically dropped by the system), because referenced objects could be dropped (drop dependencies), and because FOREIGN KEY constraints are not enforced, a simplified CREATE TABLE syntax can be used:

CREATE TABLE #[#]table_name
(column_definition
    [, next_column_definition]...)

Note In addition to naming a temporary table with # or ##, temporary tables can be created without the pound sign by qualifying the table name with tempdb as the database. If created in tempdb, the table will exist until SQL Server is restarted.

Stored procedures can reference temporary tables that are created during the current session. If a temporary table is created by a stored procedure or created within an EXECUTE statement, it is deleted when the procedure or EXECUTE is completed. Within a stored procedure, you cannot create a temporary table, drop it, and then create a new temporary table that has the same name.

If user-defined datatypes are used within a temporary table's definition, the user-defined datatypes must first be added to tempdb. There are two ways to add a user-defined datatype (or any other object) to the tempdb database:

• To add an object for the current session only, execute the sp_addtype system stored procedure while using tempdb.
• To permanently add a user-defined datatype, execute the sp_addtype system stored procedure in the model database and then restart SQL Server so that model is copied to tempdb.

If you execute system stored procedures from within the tempdb database, system stored procedures such as the sp_help system stored procedure will work only on temporary tables.