SQL Server 临时表与表变量 [英] SQL Server Temp table vs Table Variable

问题描述

我们客户的数据库管理员要求我们不要在报告存储过程 (#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?

此外，如果我创建一个表为 #table，而不是 ##table，则带有 # 的表是一个会话表，而不是全局的 ## ，对吗?当存储过程完成后，你不做DROP TABLE #table ... #table 还存在吗?如果它是基于会话的，那么我还能再访问它吗?

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?

推荐答案

与临时表相比，表变量可以导致更少的存储过程重新编译(请参阅 KB #243586 和 KB #305977)，并且——因为它们不能回滚——不要打扰事务日志.

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.

##table 属于全局临时表.是 #table 不存在，因为它只在给定的范围内，你永远不会在给定的范围外访问它.

##table is belogs to global temporary table. yes #table not exist because its in given scope only and you never access it out the given scope.

我还想指出使用 CTE(通用表表达式)，因为它也以某种方式用作临时表.查看此答案以获取详细信息:哪些性能更​​高，CTE 表还是临时表?

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?

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