SQL Server 2005 和临时表作用域
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/892351/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server 2005 and temporary table scope
提问by Dafydd Giddins
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.
我会假设临时表属于对存储过程的调用范围，但我想在我走这条路之前确定一下。
回答by Andomar
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
See this MSDN articlefor more information.
有关更多信息，请参阅此MSDN 文章。
回答by Martynnw
The temporary table will be accesible to the instance of the procedure that creates it
临时表将可供创建它的过程实例访问
The following script
下面的脚本
Exec ('Select 1 as col Into #Temp Select * From #Temp')
Exec ('Select 2 as col Into #Temp Select * From #Temp')
Returns
退货
Col
1
Col
2
Not
不是
Col
1
2
Or an error because the table already exists.
或者因为表已经存在而出错。
The temporary table will also be accesible by any 'child' procedures that the initial procedure runs as well.
临时表也可以被初始过程运行的任何“子”过程访问。
回答by A-K
The folowing article might help: "How to Share Data Between Stored Procedures" http://www.sommarskog.se/share_data.html
以下文章可能会有所帮助：“如何在存储过程之间共享数据” http://www.sommarskog.se/share_data.html
回答by Scott Anderson
You might also think about using table variables. They have a very well-defined scope, and they are sometimes faster than their temporary table counterparts. The only problem with table variables is that they cannot be indexed, so some performance could be lost despite their nature. Check herefor some more information on the subject.
您可能还会考虑使用表变量。它们有一个非常明确的作用域，有时它们比临时表的对应物更快。表变量的唯一问题是它们不能被索引，因此尽管它们的性质可能会损失一些性能。 在此处查看有关该主题的更多信息。
