sql临时表@tmp vs #tmp

Question

在SQL 2005中，@tmp和#tmp这两种类型的临时表有什么不同？还有我不知道的其他类型吗？

谢谢

Answer 1

#tmp是一个临时表，在很大程度上就像一个真实的表。它可以有索引，可以有统计数据，可以参与事务，优化器将计算出正确的行估计

@tmp是一个表变量。没有索引，没有统计数据，没有事务感知，优化器总是假设只有1行

否则，它们都是作用域(略有不同)，在内存/缓存中，但上下文是临时数据库，如果太大，将溢出到磁盘等

编辑：

关于表变量上的键。他们没有什么不同。没有统计数据，假设只有一行。它会将表扫描更改为聚集索引扫描，这是相同的。检查所有查询计划和估计行。

另外，只需阅读这段What a difference a temp table makes over a table variable

我做的第一件事是在@ComputersToProcess表变量上放置一个主键。这将表扫描转换为聚集索引扫描，但对性能没有任何影响。

Answer 2

请参阅http://support.microsoft.com/kb/305977

与临时表相比，表变量具有以下优点：

正如SQL Server联机丛书“

• ”文章中所提到的，与只在表变量更新期间涉及表变量的临时tables.
• Transactions相比，表变量(如局部变量)有一个明确定义的作用域，在作用域结束时它们会自动成为变量，从而导致存储过程的重新编译次数较少。因此，表变量需要较少的锁定和日志记录资源。由于表变量的作用域有限，并且不是持久性数据库的一部分，因此them.

事务回滚不会影响

与临时表相比，以下是一些缺点：

除了为PRIMARY或UNIQUE约束创建的系统索引之外，不能对表变量创建

• Non-clustered索引。与具有非集群indexes.
• Table变量的临时表相比，这可能会影响查询性能。不像临时表那样维护统计信息。不能通过自动创建或使用CREATE Statistics语句在表变量上创建统计信息。因此，对于大型表上的复杂查询，缺少统计信息可能会阻止优化器确定查询的最佳计划，从而影响query.
• The表定义的性能。在初始声明statement.
• Tables变量不能用于INSERT EXEC或SELECT INTO statement.
• CHECK约束、表类型声明中的默认值和计算列不能调用用户定义的functions.
• You不能使用EXEC语句或sp_executesql存储过程运行引用表变量的动态SQL Server查询后，表定义不能更改。如果表变量是在EXEC语句或sp_executesql存储过程之外创建的。由于表变量只能在其局部作用域中引用，因此EXEC语句和sp_executesql存储过程将超出表变量的作用域。但是，您可以创建表变量并在EXEC语句或sp_executesql存储过程中执行所有处理，因为这样表变量local scope就在EXEC语句或sp_executesql存储过程中。

与临时表或永久表相比，确保性能更好的表变量是否仅限于内存结构，因为它们是在驻留在物理磁盘上的数据库中维护的？

• 表变量不是仅限内存的结构。因为一个表变量可能容纳的数据超过了内存的容量，所以它必须在磁盘上有一个位置来存储数据。表变量是在临时数据库中创建的，类似于临时表。如果内存可用，表变量和临时表都是在内存(数据缓存)中创建和处理的。

有关实际性能比较，另请参阅：

• http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx
• http://sqlnerd.blogspot.com/2005/09/temp-tables-vs-table-variables.html
• http://www.sqlservercentral.com/articles/63472/

Answer 3

只是在现有答案的基础上进行添加。实际上有3类型的临时表。除了其他答案之外，您还可以像这样创建全局临时表##globalTempTable

它们对所有sql server连接都是可见的，并且很少使用，但对于指出它们确实存在很有用。

下面是关于标准临时表和全局临时表http://www.codeproject.com/KB/database/TempTable.aspx之间的区别的很好的读物