表变量和临时表

 表变量存储在内存中，而临时表存储在tempdb中，会涉及到物理IO读写，那么我们是否可以由此得出结论，使用表变量要比使用临时表效率高呢？相信有一部分人会和我有同样的想法，使用表变量的效率高，真是如此吗？先从一次优化存储过程的经历说起。

      存储过程涉及到两个表，一个是用户今日积分表@tableUserScore（数据源来自用户积分详情表中的今日数据)，一个是用户积分统计表UserScoreSum，该存储过程逻辑就是统计@tableUserScore中用户不同原因的积分值，生成到表UserScoreSum中。数据量不算很大，@tableUserScore中大概40万条，但这个存储过程执行时间却有些惊人，通常都在1个小时之上。优化的最终结果是将表变量@tabeUserScore换成了临时表#tableUserScore，并在userid和reason上添加了联合索引，优化的效果是执行时间控制在了40S左右。临时表和表变量效率相差百倍，这次优化经历让我对临时表和表变量有了重新认识，也有了一连串的疑问，它们是如何存储的，效率如何，如何选用？

declare @tableUserScore table(
    userid int, --用户编号
    name varchar(10), --用户姓名
    reason varchar(32), --积分原因
    score int --积分值
)

create table UserScoreSum(
    userid int, --用户编号
    name varchar(10), --用户姓名
    createTime datetime, --时间
    reason1Score int, --原因1积分值
    reason2Score int, --原因2积分值
    reason3Score int, --原因3积分值
    reason4Score int, --原因4积分值
)

    以下是个人翻阅资料后的理解，总结出来希望能给和我有同样认识的人提个醒，起到抛砖引玉的作用，也希望大家对理解错误之处提出指正。

     临时表

     临时表有两种类型：本地表和全局表。在与首次创建或引用表时相同的 SQL Server 实例连接期间，本地临时表只对于创建者是可见的。当用户与 SQL Server 实例断开连接后，将删除本地临时表。全局临时表在创建后对任何用户和任何连接都是可见的，当引用该表的所有用户都与 SQL Server 实例断开连接后，将删除全局临时表。本地临时表的名称都是以“#”为前缀，全局临时表的名称都是以“##”为前缀。

     临时表存储在tempdb中，因此临时表的访问是有可能造成物理IO的，当然在修改时也需要生成日志来确保一致性，同时锁机制也是不可缺少的。

     临时表可以创建索引，也可以定义统计数据，所以可以用数据定义语言(DDL)的声明来阻止临时表添加的限制，约束，并参照完整性，如主键和外键约束。

     表变量

     表变量是变量的一种，表变量也分为本地及全局的两种，本地表变量的名称都是以“@”为前缀，只有在本地当前的用户连接中才可以访问。全局的表变量的名称都是以“@@”为前缀，一般都是系统的全局变量，像我们常用到的，如@@Error代表错误的号，@@RowCount代表影响的行数。

     表变量存放在内存中，正是因为这一点所有用户访问表变量的时候SQL Server是不需要生成日志。同时变量是不需要考虑其他会话访问的问题，因此也不需要锁机制，对于非常繁忙的系统来说，避免锁的使用可以减少一部分系统负载。[表变量存放在内存是有一定限制的，如果表变量数据量超过阈值，会把内存耗尽，然后使用TempDB的空间，这样主要还是使用硬盘空间，但同时把内存基本耗尽，增加了内存调入调出的机会，反而降低速度]

     表变量另外还有一个限制就是不能创建索引，当然也不存在统计数据的问题，因此在用户访问表变量的时候也就不存在执行计划选择的问题了(也就是以为着编译阶段后就没有优化阶段了)，这一特性有的时候是件好事，而有些时候却会造成一些麻烦。

     临时表 vs. 表变量

     1.存储位置：临时表是利用了硬盘(tempdb数据库) ，表名变量是占用内存，因此小数据量当然是内存中的表变量更快。当大数据量时，就不能用表变量了，太耗内存了。大数据量时适合用临时表。

     2.性能：不能一概而论，表变量存储数据有个性能临界点，在这个临界点之内，表变量比临时表快，表变量是存储在内存中的。

     3.索引：表变量不支持索引和统计数据，但可以有主键；临时表则可以支持索引和统计数据。

     我们对于较小的临时计算用数据集考虑使用表变量。如果数据集比较大，如果在代码中用于临时计算，同时这种临时使用永远都是简单的全数据集扫描而不需要考虑什么优化，比如说没有分组或分组很少的聚合(比如说COUNT、SUM、AVERAGE、MAX等)，也可以考虑使用表变量。使用表变量另外一个考虑因素是应用环境的内存压力，如果代码的运行实例很多，就要特别注意内存变量对内存的消耗。一般对于大的数据集我们最好使用临时表，同时创建索引。