sql-server – SQL Server中的临时表和表变量有什么区别？
|
这似乎是一个有很多神话和矛盾观点的领域. 那么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)
对不同范围的可见性 @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函数不起作用,内部名称完全是系统生成的,与变量名称无关.下面通过键入(希望是唯一的)列名称来演示元数据仍然存在.对于没有唯一列名的表,只要它们不为空,就可以使用 /*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解释). （编辑：安卓应用网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |