SQL Server 临时表与表变量
我们客户的数据库管理员要求我们不要在报告存储过程 (#Table) 中使用临时表，而是使用表变量。
表变量是否比临时表效率低？
另外，如果我创建一个表为 #table，而不是 ##table，则带有一个 # 的表是会话表，如下所示与全局的 ## 相反，对吗？当存储过程完成时，并且您不执行DROP TABLE #table ...#table仍然存在吗？如果它是基于会话的，那么我还能再次访问它吗？
Our client's database admins have requested that we don't use temp tables within our reporting stored procedures (#Table), but instead, make use of table variables.
Are table variables less efficient than temp tables?
Also, if I create a table as #table, as opposed to ##table, the one with one # is a session table, as opposed to the ## which is global, right? When the stored procedure is completed, and you don't do a DROP TABLE #table ... does #table still exist? If it's session based, then will I ever have access to it again?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
与临时表相比，表变量可能会导致更少的存储过程重新编译（请参阅知识库 #243586 和知识库 #305977），并且 - 因为它们无法回滚 - 不关心事务日志。
##table属于全局临时表。是的 #table 不存在，因为它仅在给定范围内，并且您永远不会在给定范围之外访问它。编辑
我还想指出使用 CTE（通用表表达式），因为它也以某种方式充当临时表。
检查此答案以了解详细信息：CTE 或临时表哪个性能更高？
Table variables can lead to fewer stored procedure recompilations than temporary tables (see KB #243586 and KB #305977), and — since they cannot be rolled back — do not bother with the transaction log.
##tableis belogs to global temporary table. yes #table not exist because its in given scope only and you never access it out the given scope.Edit
I also like to point make use of CTE(Common Table Expressions) because it also somehow work as temporary table.
Check this answer for detail : Which are more performant, CTE or temporary tables?
我不是 100% 确定你在问什么，因为你的标题提到了表变量，所以你被要求使用表变量，但你的问题没有询问任何关于表变量的问题......但是表变量的声明如下：
I'm not 100% sure what you're asking, since your title mentions Table Variables, you're asked to use Table Variables, but your question asks nothing about Table Variables... But table variables are declared like:
如果本地临时表（#table）是在 SP 中创建的，则在 SP 完成后将删除它。 BOL 说：
临时表超出范围时会自动删除，
除非使用 DROP TABLE 显式删除：
删除在存储过程中创建的本地临时表
当存储过程完成时自动执行。该表可以是
由存储执行的任何嵌套存储过程引用
创建表的过程。该表不能被引用
调用创建该表的存储过程的进程。
所有其他本地临时表都会自动删除
当前会话结束。
会话时全局临时表会自动删除
创建表的任务结束，所有其他任务都已停止
引用它们。任务和表之间的关联是
仅在单个 Transact-SQL 语句的生命周期内维护。这
意味着全局临时表在完成时被删除
主动引用的最后一个 Transact-SQL 语句
创建会话结束时的表。
If local temporary table (#table) was created in SP, it is dropped after SP is finished. BOL says:
Temporary tables are automatically dropped when they go out of scope,
unless explicitly dropped by using DROP TABLE:
A local temporary table created in a stored procedure is dropped
automatically when the stored procedure is finished. 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 that 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.