在SQL Server 2005中,有两种方法可以创建临时表:
declare @tmp table (Col1 int, Col2 int);
or
create table #tmp (Col1 int, Col2 int);
这两者之间有什么区别?关于@tmp是否仍然使用tempdb,或者是否所有事情都发生在内存中,我读过相互矛盾的意见。
在哪些情况下,一个会优于另一个?
在SQL Server 2005中,有两种方法可以创建临时表:
declare @tmp table (Col1 int, Col2 int);
or
create table #tmp (Col1 int, Col2 int);
这两者之间有什么区别?关于@tmp是否仍然使用tempdb,或者是否所有事情都发生在内存中,我读过相互矛盾的意见。
在哪些情况下,一个会优于另一个?
当前回答
Temp table: A Temp table is easy to create and back up data. Table variable: But the table variable involves the effort when we usually create the normal tables. Temp table: Temp table result can be used by multiple users. Table variable: But the table variable can be used by the current user only. Temp table: Temp table will be stored in the tempdb. It will make network traffic. When we have large data in the temp table then it has to work across the database. A Performance issue will exist. Table variable: But a table variable will store in the physical memory for some of the data, then later when the size increases it will be moved to the tempdb. Temp table: Temp table can do all the DDL operations. It allows creating the indexes, dropping, altering, etc.., Table variable: Whereas table variable won't allow doing the DDL operations. But the table variable allows us to create the clustered index only. Temp table: Temp table can be used for the current session or global. So that a multiple user session can utilize the results in the table. Table variable: But the table variable can be used up to that program. (Stored procedure) Temp table: Temp variable cannot use the transactions. When we do the DML operations with the temp table then it can be rollback or commit the transactions. Table variable: But we cannot do it for table variable. Temp table: Functions cannot use the temp variable. More over we cannot do the DML operation in the functions . Table variable: But the function allows us to use the table variable. But using the table variable we can do that. Temp table: The stored procedure will do the recompilation (can't use same execution plan) when we use the temp variable for every sub sequent calls. Table variable: Whereas the table variable won't do like that.
其他回答
另一个区别:
表var只能从创建它的过程中的语句中访问,而不能从该过程调用的其他过程或嵌套的动态SQL(通过exec或sp_executesql)访问。
另一方面,临时表的作用域包括调用过程和嵌套动态SQL中的代码。
如果由过程创建的表必须可以从其他被调用的过程或动态SQL中访问,则必须使用临时表。这在复杂的情况下非常方便。
@wcm -实际上nit选择表变量不只是Ram -它可以部分存储在磁盘上。
临时表可以有索引,而表变量只能有主索引。如果速度是一个问题,表变量可以更快,但显然如果有很多记录,或者需要搜索聚集索引的临时表,那么临时表将更好。
好的背景文章
Temp table: A Temp table is easy to create and back up data. Table variable: But the table variable involves the effort when we usually create the normal tables. Temp table: Temp table result can be used by multiple users. Table variable: But the table variable can be used by the current user only. Temp table: Temp table will be stored in the tempdb. It will make network traffic. When we have large data in the temp table then it has to work across the database. A Performance issue will exist. Table variable: But a table variable will store in the physical memory for some of the data, then later when the size increases it will be moved to the tempdb. Temp table: Temp table can do all the DDL operations. It allows creating the indexes, dropping, altering, etc.., Table variable: Whereas table variable won't allow doing the DDL operations. But the table variable allows us to create the clustered index only. Temp table: Temp table can be used for the current session or global. So that a multiple user session can utilize the results in the table. Table variable: But the table variable can be used up to that program. (Stored procedure) Temp table: Temp variable cannot use the transactions. When we do the DML operations with the temp table then it can be rollback or commit the transactions. Table variable: But we cannot do it for table variable. Temp table: Functions cannot use the temp variable. More over we cannot do the DML operation in the functions . Table variable: But the function allows us to use the table variable. But using the table variable we can do that. Temp table: The stored procedure will do the recompilation (can't use same execution plan) when we use the temp variable for every sub sequent calls. Table variable: Whereas the table variable won't do like that.
还要考虑到,您通常可以用派生表来替换这两个表,这也可能更快。但是,与所有性能调优一样,只有针对实际数据的实际测试才能告诉您特定查询的最佳方法。
令我惊讶的是,没有人提到这两者之间的关键区别是临时表支持并行插入,而表变量不支持。您应该能够从执行计划中看出不同之处。下面是9频道和MSDN doc上的SQL workshop的视频。
这也解释了为什么应该对较小的表使用表变量,否则应该使用临时表,正如SQLMenace前面回答的那样。