临时表VS表变量:因地制宜,合理使用(1)
一直以来大家对临时表与表变量的孰优孰劣争论颇多,一些技术群里的朋友甚至认为表变量几乎一无是处,比如无统计信息,不支持事务等等.但事实并非如此.这里我就临时表与表变量做个对比,对于大多数人不理解或是有歧义的地方进行详细说明.
注:这里只讨论一般临时表,对全局临时表不做阐述.
生命周期
临时表:会话中,proc中,或使用显式drop
表变量:batch中
这里用简单的code说明表变量作用域
- DECLARE @t TABLE(i int) ----定义表变量@t
- SELECT *FROM @t -----访问OK
- insert into @t select 1 -----插入数据OK
- select * from @t -------访问OK
- go -------结束批处理
- select * from @t -------不在作用域出错
注意:虽然说sqlserver在定义表变量完成前不允许你使用定义的变量.但注意下面情况仍然可正常运行!
- if 'a'='b'
- begin
- DECLARE @t TABLE(i int)
- end
- SELECT *FROM @t -----仍然可以访问!
日志机制
临时表与表变量都会记录在tempdb中记录日志
不同的是临时表的活动日志在事务完成前是不能截断的.
这里应注意的是由于表变量不支持truncate,所以完全清空对象结果集时临时表有明显优势,而表变量只能delete
事务支持
临时表:支持
表变量:不支持
我们通过简单的实例加以说明
- create table #t (i int)
- declare @t table(i int)
- BEGIN TRAN ttt
- insert into #t select 1
- insert into @t select 1
- SELECT * FROM #t ------returns 1 rows
- SELECT * FROM @t ------returns 1 rows
- ROLLBACK tran ttt
- SELECT * FROM #t -------no rows
- SELECT * FROM @t -------still 1 rows
- drop table #t ----no use drop @t in session
锁机制(select)
临时表 会对相关对象加IS(意向共享)锁
表变量 会对相关对象加SCH-S(架构共享)锁(相当于加了nolock hint)
可以看出虽说锁的影响范围不同,但由于作用域都只是会话或是batch中,临时表的IS锁虽说兼容性不如表变量的SCH-S但绝大多数情况基本无影响.
感兴趣的朋友可以用TF1200测试
索引支持
临时表 支持
表变量 条件支持(仅SQL2014)
没错,在sql2014中你可以在创建表的同时创建索引 图1-1
注:在sql2014之前表变量只支持创建一个默认的唯一性约束
cod
- DECLARE @t TABLE
- (
- col1 int index inx_1 CLUSTERED,
- col2 int index index_2 NONCLUSTERED,
- index index_3 NONCLUSTERED(col1,col2)
- )
图1-1
- CREATE FUNCTION TVP_Customers (@cust nvarchar(10))
- RETURNS TABLE
- AS
- RETURN
- (SELECT RowNum, CustomerID, OrderDate, ShipCountry
- FROM BigOrders
- WHERE CustomerID = @cust);
- GO
- CREATE FUNCTION TVF_Customers (@cust nvarchar(10))
- RETURNS @T TABLE (RowNum int, CustomerID nchar(10), OrderDate date,
- ShipCountry nvarchar(30))
- AS
- BEGIN
- INSERT INTO @T
- SELECT RowNum, CustomerID, OrderDate, ShipCountry
- FROM BigOrders
- WHERE CustomerID = @cust
- RETURN
- END;
- DBCC FREEPROCCACHE
- GO
- SELECT * FROM TVF_Customers('CENTC');
- GO
- SELECT * FROM TVP_Customers('CENTC');
- GO
- SELECT * FROM TVF_Customers('SAVEA');
- GO
- SELECT * FROM TVP_Customers('SAVEA');
- GO
- select b.text,a.execution_count,a.* from sys.dm_exec_query_stats a
- cross apply sys.dm_exec_sql_text(a.sql_handle) b
- where b.text like '%_Customers%'
图1-2
其它方面
表变量不支持select into,alter,truncate,dbcc等
表变量不支持table hint 如(force seek)
执行计划预估
我想这里可能是引起使用何种方式争论比较突出的地方,由于表变量没有统计信息,无法添加索引等使得大家对其在执行计划中的性能表现嗤之以鼻,但实际情况呢?我们需要深入分析.
关于临时表的预估这里我就不做介绍了,主要对表变量的预估做详细阐述.
表变量在sql2000引入的一个原因就是为了在一些执行过程中减少重编译.以获得更好的性能.当然带来好处的同时也会带来一定弊端.由于其不涉及重编译,优化器其实并不知道表变量中的具体行数,此时他采取了保守的预估方式:预估行数为1行.如图2-1
Code
- declare @t table (i int)
- select * from @t-----此时0行预估行数为1行
- insert into @t select 1
- select * from @t-----此时1行,预估行数仍为1行
- insert into @t values (2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(14),(15),(16),(17),(18),(19),(20)
- select * from @t ----此时19行,预估行数仍为1行
- --....无论实际@t中有多少行,由于没有重编译,预估均为1行
图2-1
所以当我们加上重编译的的操作,此时优化器就知道了表变量的具体行数.如图2-2
Code
- declare @t table (i int)
- select * from @t option(recompile)-----此时0行预估行数为1行
- insert into @t select 1
- select * from @t option(recompile)-----此时1行,预估行数为1行
- insert into @t values (2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(14),(15),(16),(17),(18),(19),(20)
- select * from @t option(recompile)----此时19行,预估行数为19行
- --....当加入重编译hint时,优化器就知道的表变量的行数.
图2-2
至此,我们可以看到优化器知道了表变量中的行数.这样在表变量扫描的过程中,尤其针对数据量较大的情形,不会因为预估总是1而引起一些问题.
如果你刚知道这里的预估原理,现有的代码都加上重编译那工作量可想而知了..这里介绍一个新的跟踪标记,Trace Flag 2453.
TF2453可以一定程度上替代重编译Hint,但只是在非简单计划trivial plans)的情形下
注:TF2453只在sql2012 SP2和SQL2014中的补丁中起作用