logo标签列表

在SQL Server中，临时表和表变量有什么区别？

sql-servertemp-tablestable-variable
425
在SQL Server 2005中，我们可以以两种不同的方式创建相似的表。
我们可以使用表变量：
declare @tmp table (Col1 int, Col2 int);

或者我们可以使用一个临时表：
create table #tmp (Col1 int, Col2 int);

这两者之间有什么区别？我看到了关于@tmp是否仍然使用tempdb，或者一切是否在内存中进行的不同观点。
在哪些情况下，一个比另一个表现更好？
15
请看我在这里的回答。 - Martin Smith
4
这里有一篇非常好的文章，作者是 Pinal Dave，链接在这里：http://blog.sqlauthority.com/2009/12/15/sql-server-difference-temptable-and-table-variable-temptable-in-memory-a-myth/。 - sam yi
13个回答
4
临时表 (##temp/#temp) 和 表变量 (@table) 的区别是:

  1. 表变量 (@table) 在 内存 中创建。而 临时表 (##temp/#temp) 在 tempdb 数据库中创建。但是，如果存在内存压力，则属于表变量的页可能会被推送到 tempdb 中。

  2. 表变量 不能参与 事务、日志记录或锁定。这使得 @table 比 #temp 快。因此，表变量比临时表更快。

  3. 临时表 允许模式修改，而 表变量 不允许此操作。

  4. 临时表 可见于创建的例程以及子例程中。而表变量仅在创建的例程中可见。

  5. 临时表 允许 CREATE INDEXes，而 表变量 不允许 CREATE INDEX，但可以使用 Primary Key 或 Unique Constraint 创建索引。

表变量不是在内存中创建的，而是存储在 tempdb 中。 - Pratik Bhattacharya
1
@PratikBhattacharya - 在 MS SQL 2014 中引入了一种特殊类型的表变量，即“内存优化表变量”。它们不使用 tempdb。参考 - https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/faster-temp-table-and-table-variable-by-using-memory-optimization?redirectedfrom=MSDN&view=sql-server-ver15 - Litisqe Kumar
2

还要考虑到，您通常可以用派生表替换两个表，这可能会更快。然而，与所有性能调整一样，只有针对实际数据的实际测试才能告诉您特定查询的最佳方法。

1

在SQL中，临时表存储在TempDB中，本地临时表仅在当前会话中可见，而在另一个会话中不可见。这可以在嵌套的存储过程调用之间共享。全局临时表对所有其他会话可见，并且当引用表的最后一个连接关闭时销毁。例如：

Select Dept.DeptName, Dept.DeptId, COUNT(*) as TotalEmployees
into #TempEmpCount
from Tbl_EmpDetails Emp
join Tbl_Dept Dept
on Emp.DeptId = Dept.DeptId
group by DeptName, Dept.DeptId

表变量与临时表类似，也是在TempDB中创建的。表变量的作用域是声明它的批处理、存储过程或语句块。它们可以作为参数在过程之间传递。使用表变量可以编写相同的查询。

Declare @tblEmployeeCount table
(DeptName nvarchar(20),DeptId int, TotalEmployees int)
Insert @tblEmployeeCount
Select DeptName, Tbl_Dept.DeptId, COUNT(*) as TotalEmployees
from Tbl_EmpDetails
join Tbl_Dept
on Tbl_EmpDetails.DeptId = Tbl_Dept.DeptId
group by DeptName, Tbl_Dept.DeptId
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接