SQL Server 2005 和临时表作用域 [英] SQL Server 2005 and temporary table scope

问题描述

我已经阅读了有关临时表和范围的主题，我看到的所有答案似乎都没有谈到我的担忧之一.

I've read around the subject of temporary tables and scope and all the answers i've seen don't seem to talk about one of my concerns.

我了解本地临时表的范围仅在存储过程或子存储过程的生命周期内有效.然而，关于并发的情况如何.即，如果我有一个存储过程，它创建一个临时表，该临时表从两个不同的进程调用，但来自相同的用户/连接字符串，那么该临时表将在对该存储过程的两次调用之间共享，还是会出现每次调用存储过程都会创建一个唯一的临时表实例.

I understand that a local temporary table's scope is only valid withing the lifetime of a stored procedure or child stored procedures. However what is the situation with regard to concurency. i.e. if i have a stored procedure that creates a temporary table which is called from two different processes but from the same user/connection string, will that temporary table be shared between the two calls to that one stored procedure or will it be a case of each call to the stored procedure creates an unique temporary table instance.

我会假设临时表属于对存储过程的调用范围，但我想在我走这条路之前确定.

I would assume that the temporary table belongs to the scope of the call to the stored procdure but i want to be sure before i go down a path with this.

推荐答案

本地临时表(以 # 开头)仅限于您的会话；其他会话，即使来自相同的用户/连接字符串，也无法看到它们.生存期的规则取决于本地临时表是否是在存储过程中创建的:

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:

• 在存储过程中创建的本地临时表在存储过程结束时被删除；其他存储过程或调用进程看不到它们.
• 其他本地临时表在会话结束时被删除.

全局临时表(以## 开头)在会话之间共享.它们在以下情况下被丢弃:

Global temporary tables (start with ##) are shared between sessions. They are dropped when:

• 创建它们的会话结束
• 并且没有其他会话提到他们

这个命令可以方便地查看存在哪些临时表:

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

有关详细信息，请参阅此 MSDN 文章.

See this MSDN article for more information.

这篇关于SQL Server 2005 和临时表作用域的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！