SQL临时表的生存期问题

临时表有两种, local temporary table和global temporary table.

Local Temp Table只对当前的session可见. Local Temp Table的名字前面有一个井号(#).

Global Temp Table对所有的session都可见. Globel Temp Table的名字前面有两个井号(##).

临时表在超出scope的时候会自动地drop掉, 除非显式地使用DROP TABLE命令.

MSDN描述临时表生存期如下:

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.

这篇文章对local temp table做了非常好的说明, 例子很多, 很详细.