那么sql Server中的表变量和本地临时表有什么区别？
解决方法
警告
这个答案讨论了sql Server 2000中引入的“经典”表变量.内存中的sql Server 2014 OLTP引入了内存优化表类型.这些表变量实例在许多方面与下面讨论的实例不同！ (more details).
存储位置
没有不同.两者都存储在tempdb中.
我已经看到它表明,对于表变量,情况并非总是如此,但可以从下面进行验证
DECLARE @T TABLE(X INT) INSERT INTO @T VALUES(1),(2) SELECT sys.fn_PhysLocFormatter(%%physloc%%) AS [File:Page:Slot] FROM @T
示例结果(显示tempdb中存储2行的位置)
File:Page:Slot ---------------- (1:148:0) (1:148:1)
逻辑位置
@table_variables表现得更像是当前数据库的一部分而不是#temp表.对于表变量(自2005年起),如果未明确指定,则列排序规则将是当前数据库的排序规则,而对于#temp表,它将使用tempdb(More details)的默认排序规则.此外,用户定义的数据类型和XML集合必须位于tempdb中才能用于#temp表,但表变量可以在当前数据库中使用它们(Source).
sql Server 2012引入了包含的数据库. the behavior of temporary tables in these differs(h / t Aaron)
In a contained database temporary table data is collated in the collation of the contained database.
- All Metadata associated with temporary tables (for example,table and column names,indexes,and so on) will be in the catalog collation.
- Named constraints may not be used in temporary tables.
- Temporary tables may not refer to user-defined types,XML schema collections,or user-defined functions.
对不同范围的可见性
@table_variables只能在声明它们的批处理和范围内访问.在子批次中可以访问#temp_tables(嵌套触发器,过程,exec调用).在外部作用域创建的#temp_tables(@@ NESTLEVEL = 0)也可以跨越批次,因为它们会持续到会话结束.这两种类型的对象都不能在子批处理中创建并在调用范围中访问,但是如下所述(全局##临时表可以).
一生
当包含DECLARE @ .. TABLE语句的批处理执行时(在该批处理中的任何用户代码运行之前)并在最后隐式删除,将隐式创建@table_variables.
虽然解析器不允许您在DECLARE语句之前尝试使用表变量,但隐式创建可以在下面看到.
IF (1 = 0) BEGIN DECLARE @T TABLE(X INT) END --Works fine SELECT * FROM @T
遇到Tsql CREATE TABLE语句时会显式创建#temp_tables,并且可以使用DROP TABLE显式删除,或者在批处理结束时隐式删除#temp_tables(如果在子批处理中创建@@ NESTLEVEL> 0)或会话结束时除此以外.
注意：在存储例程中,两种类型的对象can be cached而不是重复创建和删除新表.这种缓存何时会出现限制,但是#temp_tables可能会违反,但@table_variables的限制无论如何都会受到限制.缓存的#temp表的维护开销略大于表变量as illustrated here.
对象元数据
对于两种类型的对象,这基本相同.它存储在tempdb中的系统基表中.然而,查看#temp表更为直接,因为OBJECT_ID(‘tempdb .. #T’)可用于键入系统表,而内部生成的名称与CREATE TABLE语句中定义的名称更紧密相关.对于表变量,object_id函数不起作用,内部名称完全是系统生成的,与变量名称无关.下面通过键入(希望是唯一的)列名称来演示元数据仍然存在.对于没有唯一列名的表,只要它们不为空,就可以使用DBCC PAGE确定object_id.
/*Declare a table variable with some unusual options.*/
DECLARE @T TABLE
(
[dba.se] INT IDENTITY PRIMARY KEY NONCLUSTERED,A INT CHECK (A > 0),B INT DEFAULT 1,InRowFiller char(1000) DEFAULT REPLICATE('A',1000),OffRowFiller varchar(8000) DEFAULT REPLICATE('B',8000),LOBFiller varchar(max) DEFAULT REPLICATE(cast('C' as varchar(max)),10000),UNIQUE CLUSTERED (A,B)
WITH (FILLFACTOR = 80,IGNORE_DUP_KEY = ON,DATA_COMPRESSION = PAGE,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)
)
INSERT INTO @T (A)
VALUES (1),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13)
SELECT t.object_id,t.name,p.rows,a.type_desc,a.total_pages,a.used_pages,a.data_pages,p.data_compression_desc
FROM tempdb.sys.partitions AS p
INNER JOIN tempdb.sys.system_internals_allocation_units AS a
ON p.hobt_id = a.container_id
INNER JOIN tempdb.sys.tables AS t
ON t.object_id = p.object_id
INNER JOIN tempdb.sys.columns AS c
ON c.object_id = p.object_id
WHERE c.name = 'dba.se'
产量
Duplicate key was ignored. +-----------+-----------+------+-------------------+-------------+------------+------------+-----------------------+ | object_id | name | rows | type_desc | total_pages | used_pages | data_pages | data_compression_desc | +-----------+-----------+------+-------------------+-------------+------------+------------+-----------------------+ | 574625090 | #22401542 | 13 | IN_ROW_DATA | 2 | 2 | 1 | PAGE | | 574625090 | #22401542 | 13 | LOB_DATA | 24 | 19 | 0 | PAGE | | 574625090 | #22401542 | 13 | ROW_OVERFLOW_DATA | 16 | 14 | 0 | PAGE | | 574625090 | #22401542 | 13 | IN_ROW_DATA | 2 | 2 | 1 | NONE | +-----------+-----------+------+-------------------+-------------+------------+------------+-----------------------+
交易
@table_variables上的操作作为系统事务执行,独立于任何外部用户事务,而等效的#temp表操作将作为用户事务本身的一部分执行.因此,ROLLBACK命令将影响#temp表,但保持@table_variable不变.
DECLARE @T TABLE(X INT) CREATE TABLE #T(X INT) BEGIN TRAN INSERT #T OUTPUT INSERTED.X INTO @T VALUES(1),(3) /*Both have 3 rows*/ SELECT * FROM #T SELECT * FROM @T ROLLBACK /*Only table variable now has rows*/ SELECT * FROM #T SELECT * FROM @T DROP TABLE #T
记录
两者都生成tempdb事务日志的日志记录.一个常见的误解是表变量不是这种情况,所以下面演示了这个脚本,它声明了一个表变量,添加了几行然后更新它们并删除它们.
因为表变量是在批处理的开始和结束时隐式创建和删除的,所以必须使用多个批处理才能查看完整的日志记录.
USE tempdb;
/*
Don't run this on a busy server.
Ideally should be no concurrent activity at all
*/
CHECKPOINT;
GO
/*
The 2nd column is binary to allow easier correlation with log output shown later*/
DECLARE @T TABLE ([C71ACF0B-47E9-4CAD-9A1E-0C687A8F9CF3] INT,B BINARY(10))
INSERT INTO @T
VALUES (1,0x41414141414141414141),(2,0x41414141414141414141)
UPDATE @T
SET B = 0x42424242424242424242
DELETE FROM @T
/*Put allocation_unit_id into CONTEXT_INFO to access in next batch*/
DECLARE @allocId BIGINT,@Context_Info VARBINARY(128)
SELECT @Context_Info = allocation_unit_id,@allocId = a.allocation_unit_id
FROM sys.system_internals_allocation_units a
INNER JOIN sys.partitions p
ON p.hobt_id = a.container_id
INNER JOIN sys.columns c
ON c.object_id = p.object_id
WHERE ( c.name = 'C71ACF0B-47E9-4CAD-9A1E-0C687A8F9CF3' )
SET CONTEXT_INFO @Context_Info
/*Check log for records related to modifications of table variable itself*/
SELECT Operation,Context,AllocUnitName,[RowLog Contents 0],[Log Record Length]
FROM fn_dblog(NULL,NULL)
WHERE AllocUnitId = @allocId
GO
/*Check total log usage including updates against system tables*/
DECLARE @allocId BIGINT = CAST(CONTEXT_INFO() AS BINARY(8));
WITH T
AS (SELECT Operation,CASE
WHEN AllocUnitId = @allocId THEN 'Table Variable'
WHEN AllocUnitName LIKE 'sys.%' THEN 'System Base Table'
ELSE AllocUnitName
END AS AllocUnitName,[Log Record Length]
FROM fn_dblog(NULL,NULL) AS D)
SELECT Operation = CASE
WHEN GROUPING(Operation) = 1 THEN 'Total'
ELSE Operation
END,[Size in Bytes] = COALESCE(SUM([Log Record Length]),0),Cnt = COUNT(*)
FROM T
GROUP BY GROUPING SETS( ( Operation,AllocUnitName ),( ) )
ORDER BY GROUPING(Operation),AllocUnitName
返回
详细的视图
As far as I’ve been able to discern两者的操作都会产生大致相同数量的日志记录.
虽然日志记录的数量非常相似,但一个重要的区别是与#temp表相关的日志记录在任何包含用户事务完成之前无法清除,因此长时间运行的事务在某些时候写入#temp表将阻止日志截断tempdb而不是为表变量生成的自治事务.
表变量不支持TRUNCATE,因此当需要从表中删除所有行时,可能处于日志记录缺点(尽管对于非常小的表,DELETE can work out better anyway)
基数
许多涉及表变量的执行计划将显示一行估计为它们的输出.检查表变量属性显示sql Server认为表变量具有零行(为什么它估计将从零行表中发出1行由@Paul White here解释).
但是,上一节中显示的结果确实显示了sys.partitions中准确的行数.问题是,在大多数情况下,引用表变量的语句在表为空时进行编译.如果在填充@table_variable之后(重新)编译语句,那么它将用于表基数(这可能由于显式重新编译而发生,或者可能是因为该语句还引用了导致延迟编译或重新编译的另一个对象. )
DECLARE @T TABLE(I INT); INSERT INTO @T VALUES(1),(5) CREATE TABLE #T(I INT) /*Reference to #T means this statement is subject to deferred compile*/ SELECT * FROM @T WHERE NOT EXISTS(SELECT * FROM #T) DROP TABLE #T
计划显示延迟编译后的准确估计行数.
在sql Server 2012 SP2中,引入了跟踪标志2453.更多细节在“关系引擎”here下.
当启用此跟踪标志时,它可以导致自动重新编译以考虑更改的基数,这很快就会进一步讨论.
注意：在兼容级别150的Azure上,语句的编译是now deferred until first execution.这意味着它将不再受零行估计问题的影响.
没有列统计信息
具有更准确的表基数并不意味着估计的行数将更准确(除非对表中的所有行执行操作). sql Server根本不维护表变量的列统计信息,因此将依赖于基于比较谓词的猜测(例如,对于非唯一列,=将返回表的10％或对于>比较,将返回30％ ).相比之下,为#temp表维护了列统计信息.
sql Server维护对每列进行的修改次数.如果编译计划后的修改数超过重新编译阈值(RT),则将重新编译计划并更新统计信息. RT取决于表的类型和大小.
从Plan Caching in SQL Server 2008
RT is calculated as follows. (n refers to a table’s cardinality when a query plan is compiled.)
Permanent table
– If n <= 500,RT = 500.
– If n > 500,RT = 500 + 0.20 * n.Temporary table
– If n < 6,RT = 6.
– If 6 <= n <= 500,RT = 500 + 0.20 * n.
Table variable
– RT does not exist. Therefore,recompilations do not happen because of changes in cardinalities of table variables.
(But see note about TF 2453 below)
KEEP PLAN提示可用于为#temp表设置与永久表相同的RT.
所有这些的净效果是,当涉及许多行时,为#temp表生成的执行计划通常比@table_variables更好,因为sql Server具有更好的信息.
NB1：表变量没有统计信息,但仍然可以在跟踪标志2453下发生“统计更改”重新编译事件(不适用于“普通”计划)这似乎发生在与上面的临时表所示相同的重新编译阈值下另外一个如果N = 0 – > RT = 1.即,当表变量为空时编译的所有语句将在非空时第一次执行时最终得到重新编译并更正TableCardinality.编译时基表基数存储在计划中,如果使用相同的基数再次执行该语句(由于控制语句的流动或重用缓存的计划),则不会进行重新编译.
NB2：对于存储过程中的缓存临时表,重新编译故事要比上面描述的复杂得多.有关所有血腥细节,请参阅Temporary Tables in Stored Procedures.
重新编译
除了上面描述的基于修改的重新编译之外,#temp表也可以与additional compiles相关联,因为它们允许对触发编译的表变量禁止的操作(例如DDL更改CREATE INDEX,ALTER TABLE)
锁定
它has been stated表变量不参与锁定.不是这种情况.将以下输出运行到SSMS消息选项卡,为insert语句获取和释放锁的详细信息.
DECLARE @tv_target TABLE (c11 int,c22 char(100))
DBCC TRACEON(1200,-1,3604)
INSERT INTO @tv_target (c11,c22)
VALUES (1,REPLICATE('A',100)),100))
DBCC TRACEOFF(1200,3604)
对于来自表变量SELECT的查询,Paul White在注释中指出这些查询会自动带有隐式NOLOCK提示.如下所示
DECLARE @T TABLE(X INT); SELECT X FROM @T OPTION (RECOMPILE,QUERYTRACEON 3604,QUERYTRACEON 8607)
产量
*** Output Tree: (trivial plan) ***
PhyOp_TableScan TBL: @T Bmk ( Bmk1000) IsRow: COL: IsBaseRow1002 Hints( NOLOCK )
然而,这对锁定的影响可能很小.
SET NOCOUNT ON;
CREATE TABLE #T( [ID] [int] IDENTITY NOT NULL,[Filler] [char](8000) NULL,PRIMARY KEY CLUSTERED ([ID] DESC))
DECLARE @T TABLE ( [ID] [int] IDENTITY NOT NULL,PRIMARY KEY CLUSTERED ([ID] DESC))
DECLARE @I INT = 0
WHILE (@I < 10000)
BEGIN
INSERT INTO #T DEFAULT VALUES
INSERT INTO @T DEFAULT VALUES
SET @I += 1
END
/*Run once so compilation output doesn't appear in lock output*/
EXEC('SELECT *,sys.fn_PhysLocFormatter(%%physloc%%) FROM #T')
DBCC TRACEON(1200,3604,-1)
SELECT *,sys.fn_PhysLocFormatter(%%physloc%%)
FROM @T
PRINT '--*--'
EXEC('SELECT *,sys.fn_PhysLocFormatter(%%physloc%%) FROM #T')
DBCC TRACEOFF(1200,-1)
DROP TABLE #T
这些都不会以索引键顺序返回结果,表明sql Server为两者使用了allocation ordered scan.
我运行了上面的脚本两次,第二次运行的结果如下
Process 58 acquiring Sch-S lock on OBJECT: 2:-1325894110:0 (class bit0 ref1) result: OK --*-- Process 58 acquiring IS lock on OBJECT: 2:-1293893996:0 (class bit0 ref1) result: OK Process 58 acquiring S lock on OBJECT: 2:-1293893996:0 (class bit0 ref1) result: OK Process 58 releasing lock on OBJECT: 2:-1293893996:0
由于sql Server刚刚获取对象的模式稳定性锁定,因此表变量的锁定输出确实非常小.但是对于一个#temp表来说,它几乎一样轻,因为它取出了一个对象级别的S锁.当然,在使用#temp表时,也可以明确指定NOLOCK提示或READ UNCOMMITTED隔离级别.
与记录周围用户事务的问题类似,可能意味着#temp表的锁保持更长时间.使用下面的脚本
--BEGIN TRAN;
CREATE TABLE #T (X INT,Y CHAR(4000) NULL);
INSERT INTO #T (X) VALUES(1)
SELECT CASE resource_type
WHEN 'OBJECT' THEN OBJECT_NAME(resource_associated_entity_id,2)
WHEN 'ALLOCATION_UNIT' THEN (SELECT OBJECT_NAME(object_id,2)
FROM tempdb.sys.allocation_units a
JOIN tempdb.sys.partitions p ON a.container_id = p.hobt_id
WHERE a.allocation_unit_id = resource_associated_entity_id)
WHEN 'DATABASE' THEN DB_NAME(resource_database_id)
ELSE (SELECT OBJECT_NAME(object_id,2)
FROM tempdb.sys.partitions
WHERE partition_id = resource_associated_entity_id)
END AS object_name,*
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID
DROP TABLE #T
-- ROLLBACK
当在两种情况下在显式用户事务之外运行时,检查sys.dm_tran_locks时返回的唯一锁是DATABASE上的共享锁.
取消注释BEGIN TRAN …返回ROLLBACK 26行,显示锁定在对象本身和系统表行上,以允许回滚并防止其他事务读取未提交的数据.等效表变量操作不受使用用户事务回滚的影响,并且无需保留这些锁以供我们检查下一个语句,但跟踪在Profiler中获取和释放的锁或使用跟踪标记1200显示仍有大量锁定事件仍然存在发生.
索引
对于sql Server 2014之前的版本,只能在表变量上隐式创建索引,这是添加唯一约束或主键的副作用.这当然意味着只支持唯一索引.可以模拟具有唯一聚簇索引的表上的非唯一非聚簇索引,只需将其声明为UNIQUE NONCLUSTERED并将CI密钥添加到所需NCI密钥的末尾(sql Server将do this behind the scenes anyway,即使非唯一NCI可能是指定)
如前所示,可以在约束声明中指定各种index_options,包括DATA_COMPRESSION,IGNORE_DUP_KEY和FILLFACTOR(尽管设置那个没有意义,因为它只会对索引重建产生任何影响,并且您无法重建表变量的索引！ )
此外,表变量不支持INCLUDEd列,过滤索引(直到2016年)或分区,#temp表do(必须在tempdb中创建分区方案).
sql Server 2014中的索引
可以在sql Server 2014的表变量定义中内联声明非唯一索引.示例语法如下.
DECLARE @T TABLE ( C1 INT INDEX IX1 CLUSTERED,/*Single column indexes can be declared next to the column*/ C2 INT INDEX IX2 NONCLUSTERED,INDEX IX3 NONCLUSTERED(C1,C2) /*Example composite index*/ );
sql Server 2016中的索引
从CTP 3.1开始,现在可以为表变量声明过滤索引.通过RTM,可能是包含的列也是允许的,尽管它们是will likely not make it into SQL16 due to resource constraints
DECLARE @T TABLE ( c1 INT NULL INDEX ix UNIQUE WHERE c1 IS NOT NULL /*Unique ignoring nulls*/ )
排比
插入(或以其他方式修改)@table_variables的查询不能具有并行计划,#temp_tables不受此限制.
有一个明显的解决方法,重写如下允许SELECT部分并行发生但最终使用隐藏的临时表(behind the scenes)
INSERT INTO @DATA ( ... )
EXEC('SELECT .. FROM ...')
从表变量as illustrated in my answer here中选择的查询中没有此类限制
其他功能差异
> #temp_tables不能在函数内使用. @table_variables可以在标量或多语句表UDF中使用.
> @table_variables不能有命名约束.
> @table_variables不能SELECT-ed INTO,ALTER-ed,TRUNCATEd或者是DBCC命令的目标,例如DBCC CHECKIDENT或SET IDENTITY INSERT,并且不支持表提示,例如WITH(FORCESCAN)
>优化器不会考虑对表变量的CHECK约束进行简化,隐含谓词或矛盾检测.
>表变量似乎不符合rowset sharing optimisation的条件,这意味着针对这些变量的删除和更新计划可能会遇到更多开销并且PAGELATCH_EX会等待. (Example)
仅限记忆？
如开头所述,两者都存储在tempdb中的页面上.但是,我没有说明在将这些页面写入光盘时是否存在任何行为上的差异.
我现在已经对此进行了少量测试,到目前为止还没有看到这样的差异.在我的sql Server 250页面实例上进行的具体测试似乎是数据文件写入之前的截止点.
NB: The behavior below no longer occurs in sql Server 2014 or 070023 the eager writer is no longer as
eager to write pages to disc. More details on that change at 070024.
运行以下脚本
CREATE TABLE #T(X INT,Filler char(8000) NULL) INSERT INTO #T(X) SELECT TOP 250 ROW_NUMBER() OVER (ORDER BY @@SPID) FROM master..spt_values DROP TABLE #T
使用Process Monitor监视对tempdb数据文件的写入时,我没有看到任何内容(除了偶尔会在数据库引导页面偏移73,728处).更改250到251I后开始看到写如下.
上面的屏幕截图显示了5 * 32页写入和一个单页写入,表明161个页面已写入光盘.在使用表变量进行测试时,我得到了相同的250页截止点.下面的脚本通过查看sys.dm_os_buffer_descriptors以不同的方式显示它
DECLARE @T TABLE (
X INT,[dba.se] CHAR(8000) NULL)
INSERT INTO @T
(X)
SELECT TOP 251 Row_number() OVER (ORDER BY (SELECT 0))
FROM master..spt_values
SELECT is_modified,Count(*) AS page_count
FROM sys.dm_os_buffer_descriptors
WHERE database_id = 2
AND allocation_unit_id = (SELECT a.allocation_unit_id
FROM tempdb.sys.partitions AS p
INNER JOIN tempdb.sys.system_internals_allocation_units AS a
ON p.hobt_id = a.container_id
INNER JOIN tempdb.sys.columns AS c
ON c.object_id = p.object_id
WHERE c.name = 'dba.se')
GROUP BY is_modified
结果
is_modified page_count ----------- ----------- 0 192 1 61
显示192页写入光盘并清除脏标志.它还表明写入光盘并不意味着页面将立即从缓冲池中逐出.对这个表变量的查询仍然可以完全从内存中得到满足.
在最大服务器内存设置为2000 MB的空闲服务器上,DBCC MEMORYSTATUS报告缓冲池页面分配为大约1,843,000 KB(约23,000页)我以1000行/页的批量插入上面的表并记录每次迭代.
SELECT Count(*)
FROM sys.dm_os_buffer_descriptors
WHERE database_id = 2
AND allocation_unit_id = @allocId
AND page_type = 'DATA_PAGE'
table变量和#temp表都提供了几乎相同的图形,并且在达到它们并非完全保留在内存中之前设置为几乎最大化缓冲池,因此似乎没有任何特定的限制如何很多内存都可以消耗.
