SQL Server Temp表与表变量

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.

我们客户的数据库管理员要求我们不在报告存储过程（#Table）中使用临时表，而是使用表变量。

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?

另外，如果我创建一个表作为#table而不是## table，那么一个＃是一个会话表，而不是##这是全局的，对吧？当存储过程完成后，你没有做DROP TABLE #table ... #table是否仍然存在？如果它是基于会话的，那么我将再次访问它吗？

3 个解决方案

#1

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.

与临时表相比，表变量可以导致更少的存储过程重新编译（参见KB＃243586和KB＃305977），并且 - 由于无法回滚 - 不要打扰事务日志。

##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.

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

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?

我还想指出使用CTE（公用表表达式），因为它也以某种方式用作临时表。请查看此答案以获取详细信息：哪些是性能更高，CTE或临时表？

#2

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:

我不是100％肯定你在问什么，因为你的标题提到表变量，你被要求使用表变量，但是你的问题没有询问表变量......但是表变量被声明为：

DECLARE @Banana TABLE 
(
  Id INT,
  Name VARCHAR(20)
)

#3

If local temporary table (#table) was created in SP, it is dropped after SP is finished. BOL says:

如果在SP中创建了本地临时表（#table），则在SP完成后将其删除。 BOL说：

Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped by using DROP TABLE:

临时表在超出范围时会自动删除，除非使用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.

  当创建表的会话结束且所有其他任务已停止引用它们时，将自动删除全局临时表。任务和表之间的关联仅在单个Transact-SQL语句的生命周期内维护。这意味着在创建会话结束时，在最后一个主动引用表的Transact-SQL语句完成时，将删除全局临时表。