在SQL Server 2008中，表变量和临时表的区别

11 浏览
0 Comments

在SQL Server 2008中，表变量和临时表的区别

可能是重复问题：
\n在SQL Server中，临时表和表变量有什么区别？ \n表变量和临时表有什么区别，实际上我有两个问题。\n

    \n

  • 如何决定何时使用哪个？

    • \n

  • 在性能上哪个更好，为什么？

    • \n

0

2 答案

0 Comments

Table variables are allocated in memory, and only when the table becomes too large, it will be assigned to the tempdb. In contrast, indexes can be created on temp tables as they are stored in the tempdb by default.

To determine whether to use a table variable or a temp table, the number of rows to be stored should be considered.

For example, to observe the page and slot location in tempdb for table variable rows, you can use the following code:

DECLARE @table TABLE (n int)
INSERT INTO @table VALUES (1), (2)
SELECT sys.fn_PhysLocFormatter(%%physloc%%) FROM @table

By running this code, you will see that the table variable rows have a page and slot location in tempdb.

0
0 Comments

在SQL Server 2008中，表变量和临时表之间存在一些差异。首先，表变量不会记录事务日志，因此它们不受事务机制的影响。这可以从以下示例中清楚地看出：

create table #T (s varchar(128)) 
declare @T table (s varchar(128)) 
insert into #T select 'old value #'
insert into @T select 'old value @'
begin transaction 
     update #T set s='new value #'
     update @T set s='new value @'
rollback transaction 
select * from #T 
select * from @T

临时表可以进行索引，这一点你没有提到。我发现这一点可能非常重要。

参考链接：http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx

0