What is the life time of a temporary table on Sql Server

Temporary Tables

You can create local and global temporary tables. Local temporary tables are visible only in the current session; global temporary tables are visible to all sessions.

Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name).

SQL statements reference the temporary table using the value specified for table_name in the CREATE TABLE statement:

CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)

INSERT INTO #MyTempTable VALUES (1)

If a local temporary table is created in a stored procedure or application that can be executed at the same time by several users, SQL Server has to be able to distinguish the tables created by the different users. SQL Server does this by internally appending a numeric suffix to each local temporary table name. The full name of a temporary table as stored in the sysobjects table in tempdb consists of table name specified in the CREATE TABLE statement and the system-generated numeric suffix. To allow for the suffix, table_name specified for a local temporary name cannot exceed 116 characters.

Temporary tables are automatically dropped when they go out of scope, unless they have already been explicitly dropped using DROP TABLE:

• A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.
• All other local temporary tables are dropped automatically at the end of the current session.
• Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.

A local temporary table created within a stored procedure or trigger is distinct from a temporary table with the same name created before the stored procedure or trigger is called. All references to the name within the stored procedure or trigger are resolved against the temporary table created in the procedure or trigger, not the version that existed before the procedure or trigger was called. Nested stored procedures can also create temporary tables with the same name as a temporary table created by the stored procedure that called it. All references to the table name in the nested stored procedure are resolved to the table created in the nested procedure, for example:

CREATE PROCEDURE Test2

AS

CREATE TABLE #t(x INT PRIMARY KEY)

INSERT INTO #t VALUES (2)

SELECT Test2Col = x FROM #t

GO

CREATE PROCEDURE Test1

AS

CREATE TABLE #t(x INT PRIMARY KEY)

INSERT INTO #t VALUES (1)

SELECT Test1Col = x FROM #t

EXEC Test2

GO

CREATE TABLE #t(x INT PRIMARY KEY)

INSERT INTO #t VALUES (99)

GO

EXEC Test1

GO

The results of running this script are:

(1 row(s) affected)

Test1Col

-----------

1

(1 row(s) affected)

Test2Col

-----------

2