临时表数据库中的生命周期

请仔细阅读



本地临时表（以＃开头）仅限于您的会话;其他会话，即使是来自同一个用户/连接字符串，也看不到它们。生命周期的规则取决于是否在存储过程中创建了本地临时表：



当存储过程中创建的本地临时表被删除程序结束;其他存储过程或调用进程无法看到它们。

会话结束时会删除其他本地临时表。

全局临时表（以＃开头） ＃）在会话之间共享。它们在以下情况下被删除：



创建它们的会话结束

并且没有其他会话指的是它们

此命令可以方便地查看存在哪些临时表：

Please read carefully

Local temporary tables (start with #) are limited to your session; other sessions, even from the same user/connection string, can''t see them. The rules for the lifetime depend on whether the local temporary table was created in a stored procedure:

A local temporary table that is created in a stored procedure is dropped when the procedure ends; other stored procedures, or the calling process, can''t see them.
Other local temporary tables are dropped when the session ends.
Global temporary tables (start with ##) are shared between sessions. They are dropped when:

The session that created them ends
AND no other session is referring to them
This command can be handy to see which temporary tables exist:

select TABLE_NAME from tempdb.information_schema.tables

如果您不确定它们是否存在，这对于删除临时表非常方便：

And this is handy to drop temporary tables if you''re not sure they exist:

if object_id('tempdb..#SoTest') is not null drop table #SoTest

您可以创建本地和全局临时表。本地临时表仅在当前会话中可见;全局临时表对所有会话都可见。



使用单个数字符号（#table_name）作为前缀的本地临时表名称，并使用双数字符号作为前缀全局临时表名称的前缀（## table_name）。



SQL语句使用CREATE TABLE语句中为table_name指定的值引用临时表：



CREATE TABLE #MyTempTable（可乐INT PRIMARY KEY）



INSERT INTO #MyTempTable VALUES（1）





如果在可由多个用户同时执行的存储过程或应用程序中创建本地临时表，则SQL Server必须能够区分创建的表由不同的用户。 SQL Server通过在每个本地临时表名称内部附加数字后缀来完成此操作。存储在tempdb中sysobjects表中的临时表的全名由CREATE TABLE语句中指定的表名和系统生成的数字后缀组成。为了允许后缀，为本地临时名称指定的table_name不能超过116个字符。



临时表在超出范围时会自动删除，除非它们已经存在使用DROP TABLE

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