临时表VS表变量--因地制宜,合理使用

一直以来大家对临时表与表变量的孰优孰劣争论颇多,一些技术群里的朋友甚至认为表变量几乎一无是处,比如无统计信息,不支持事务等等.但事实并非如此.这里我就临时表与表变量做个对比,对于大多数人不理解或是有歧义的地方进行详细说明.

注:这里只讨论一般临时表,对全局临时表不做阐述.

生命周期

临时表:会话中,PRoc中,或使用显式drop

表变量:batch中

这里用简单的code说明表变量作用域

DECLARE @t TABLE(i int) ----定义表变量@tSELECT *FROM @t        -----访问OKinsert into @t select 1 -----插入数据OKselect * from  @t      -------访问OKgo                     -------结束批处理select * from @t       -------不在作用域出错

注意:虽然说sqlserver在定义表变量完成前不允许你使用定义的变量.但注意下面情况仍然可正常运行!

if 'a'='b'beginDECLARE @t TABLE(i int)endSELECT *FROM @t        -----仍然可以访问！

日志机制

临时表与表变量都会记录在tempdb中记录日志

不同的是临时表的活动日志在事务完成前是不能截断的.

这里应注意的是由于表变量不支持truncate,所以完全清空对象结果集时临时表有明显优势,而表变量只能delete

事务支持

临时表:支持

表变量:不支持

我们通过简单的实例加以说明

create table #t (i int)declare @t table(i int)BEGIN TRAN tttinsert into #t select 1insert into @t select 1SELECT * FROM #t  ------returns 1 rowsSELECT * FROM @t  ------returns 1 rowsROLLBACK tran tttSELECT * FROM #t    -------no rowsSELECT * FROM @t    -------still 1 rowsdrop table #t       ----no use drop @t in session

锁机制(select)

临时表 会对相关对象加IS(意向共享)锁

表变量 会对相关对象加SCH-S(架构共享)锁(相当于加了nolock hint) 

可以看出虽说锁的影响范围不同,但由于作用域都只是会话或是batch中,临时表的IS锁虽说兼容性不如表变量的SCH-S但绝大多数情况基本无影响.

感兴趣的朋友可以用TF1200测试

索引支持

临时表 支持

表变量 条件支持(仅SQL2014)

没错,在sql2014中你可以在创建表的同时创建索引 图1-1

注:在sql2014之前表变量只支持创建一个默认的唯一性约束code

DECLARE @t TABLE (col1 int index inx_1 CLUSTERED, col2 int  index index_2 NONCLUSTERED,       index index_3 NONCLUSTERED(col1,col2))