SQLServer中的临时表和表变量Declare@TablenameTable
在SQL Server的性能调优中，有⼀个不可⽐⾯的问题：那就是如何在⼀段需要长时间的代码或被频繁调⽤的代码中处理临时数据集？表变量和临时表是两种选择。记得在给⼀家国内⾸屈⼀指的海运公司作SQL Server应⽤性能评估和调优的时候就看到过⼤量的临时数据集处理需求，⽽他们的开发⼈员就⽆法确定什么时候⽤临时表，什么时候⽤表变量，因此他们就简单的使⽤了临时表。实际上临时表和表变量都有特定的适⽤环境。
先卖弄⼀些基础的知识：
表变量
变量都以@或@@为前缀，表变量是变量的⼀种，另外⼀种变量被称为标量(可以理解为标准变量，就是标准数据类型的变量，例如整型int或者⽇期型DateTime)。以@前缀的表变量是本地的，因此只有在当前⽤户会话中才可以访问，⽽@@前缀的表变量是全局的，通常都是系统变量，⽐如说@@error代表最近的⼀个T-SQL 语句的报错号。当然因为表变量⾸先是个变量，因此它只能在⼀个Batch中⽣存，也就是我们所说的边界，超出了这个边界，表变量也就消亡了。
表变量存放在内存中，正是因为这⼀点所有⽤户访问表变量的时候SQL Server是不需要⽣成⽇志。同时变
量是不需要考虑其他会话访问的问题，因此也不需要锁机制，对于⾮常繁忙的系统来说，避免锁的使⽤可以减少⼀部分系统负载。
表变量另外还有⼀个限制就是不能创建索引，当然也不存在统计数据的问题，因此在⽤户访问表变量的时候也就不存在执⾏计划选择的问题了(也就是以为着编译阶段后就没有优化阶段了)，这⼀特性有的时候是件好事，⽽有些时候却会造成⼀些⿇烦。
临时表
临时对象都以#或##为前缀，临时表是临时对象的⼀种，还有例如临时存储过程、临时函数之类的临时对象，临时对象都存储在tempdb中。以#前缀的临时表为本地的，因此只有在当前⽤户会话中才可以访问，⽽##前缀的临时表是全局的，因此所有⽤户会话都可以访问。临时表以会话为边界，只要创建临时表的会话没有结束，临时表就会持续存在，当然⽤户在会话中可以通过DROP TABLE命令提前销毁临时表。
tabletable我们前⾯说过临时表存储在tempdb中，因此临时表的访问是有可能造成物理IO的，当然在修改时也需要⽣成⽇志来确保⼀致性，同时锁机制也是不可缺少的。
跟表变量另外⼀个显著去别就是临时表可以创建索引，也可以定义统计数据，因此SQL Server在处理访问临时表的语句时需要考虑执⾏计划优化的问题。
表变量 vs. 临时表
结论
综上所述，⼤家会发现临时表和表变量在底层处理机制上是有很多差别的。
简单地总结，我们对于较⼩的临时计算⽤数据集推荐使⽤表变量。如果数据集⽐较⼤，如果在代码中⽤于临时计算，同时这种临时使⽤永远都是简单的全数据集扫描⽽不需要考虑什么优化，⽐如说没有分组或分组很少的聚合(⽐如说COUNT、SUM、AVERAGE、MAX等)，也可以考虑使⽤表变量。使⽤表变量另外⼀个考虑因素是应⽤环境的内存压⼒，如果代码的运⾏实例很多，就要特别注意内存变量对内存的消耗。
⼀般对于⼤的数据集我们推荐使⽤临时表，同时创建索引，或者通过SQL Server的统计数据(Statisitcs)⾃动创建和维护功能来提供访问SQL语句的优化。如果需要在多个⽤户会话间交换数据，当然临时表就是唯⼀的选择了。需要提及的是，由于临时表存放在tempdb中，因此要注意tempdb的调优。
SQL中的临时表和表变量
我们经常使⽤临时表和表变量，那现在我们就对临时表和表变量进⾏⼀下讨论.
临时表
局部临时表
全局临时表
表变量
临时表
临时表存储在TempDB数据库中，所有的使⽤此SQL Server 实例的⽤户都共享这个TempDB，因为我们应该确保⽤来存储TempDB数据库的硬盘有⾜够的空间，以使之能够⾃⼰的增长.最好能够存储在⼀个拥有独⽴硬盘控制器上.因为这样不存在和其它的硬盘I/O进⾏争⽤.
我们很多程序员认为临时表⾮常危险，因为临时表有可能被多个连接所共享.其实在SQL Server中存在两种临时表：局部临时表和全局临时表，局部临时表(Local temp table)以#前缀来标识，并且只能被创建它的连接所使⽤.全局临时表(Global temp table)以##前缀来进⾏标识，并且可以和其它连接所共享.
局部临时表
局部临时表不能够被其它连接所共享的原因其实是在SQL Server 2000中⾃动为局部临时表的表名后⾯加上了⼀个唯⼀字符来标识.如:
CREATE TABLE [#DimCustomer_test]
(
[CustomerKey] [int]
,    [FirstName] [nvarchar](50)
,[MiddleName] [nvarchar](50)
,[LastName] [nvarchar](50)
)
现在我们来查看⼀下TempDB中 sysobjects表，我们会发现我们新创建的临时表#DimCustomer_test已经被加上了后缀:
USE TempDB
GO
SELECT name FROM sysobjects WHERE name LIKE ’%DimCustomer%’
the Result is:
name
#DimCustomer_test___________________________________________________________________________________________________000000000005 全局临时表
下⾯我们来看⼀下全局临时表:
CREATE TABLE [##DimCustomer_test]
(
[CustomerKey] [int]
,      [FirstName] [nvarchar](50)
,[MiddleName] [nvarchar](50)
,[LastName] [nvarchar](50)
)
现在我们来查看⼀下TempDB中 sysobjects表，我们会发现我们新创建的临时表##DimCustomer_test没有被加上了后缀:
USE TempDB
GO
SELECT name FROM sysobjects WHERE name LIKE ’%DimCustomer%’
The Result are:
#DimCustomer_test___________________________________________________________________________________________________000000000005 ##DimCustomer_test
--Drop test temp tables
DROP TABLE [##DimCustomer_test]
DROP TABLE [#DimCustomer_test]
可以看到我们刚才创建的全局临时表名字并没有被加上标识.
表变量
表变量和临时表针对我们使⽤⼈员来说并没有什么不同，但是在存储⽅⾯来说，他们是不同的，表变量存储在内存中.所以在性能上和临时表相⽐会更好些! 另⼀个不同的地⽅是在表连接中使⽤表变量时，要为此表变量指定别名.如:
USE AdventureWorksDW
GO
DECLARE @DimCustomer_test TABLE
(
[CustomerKey] [int]
,      [FirstName] [nvarchar](50)
,[MiddleName] [nvarchar](50)
,[LastName] [nvarchar](50)
)
---insert data to @DimCustomer_test
INSERT @DimCustomer_test
(
[CustomerKey]
,      [FirstName]
,[MiddleName]
,[LastName]
)
SELECT
[CustomerKey]
,
      [FirstName]
,[MiddleName]
,[LastName]
FROM DimCustomer
SELECT [@DimCustomer_test].CustomerKey,SUM(FactInternetSales.OrderQuantity)
FROM @DimCustomer_test  INNER JOIN FactInternetSales    ON
@DimCustomer_test.CustomerKey = FactInternetSales.CustomerKey
Group BY CustomerKey
Result:
Server: Msg 137, Level 15, State 2, Line 32
Must declare the variable ’@DimCustomer_test’.
如果我们对上⾯的查询进⾏更改，对查询使⽤别名(并且开IO):
-----in the follow script,we used the table alias.
DECLARE @DimCustomer_test TABLE
(
[CustomerKey] [int]
,      [FirstName] [nvarchar](50)
,[MiddleName] [nvarchar](50)
,[LastName] [nvarchar](50)
)
INSERT @DimCustomer_test
(
[CustomerKey]
,      [FirstName]
,[MiddleName]
,[LastName]
)
SELECT
[CustomerKey]
,      [FirstName]
,[MiddleName]
,[LastName]
FROM DimCustomer
SELECT t.CustomerKey,f.OrderQuantity
FROM @DimCustomer_test t INNER JOIN FactInternetSales  f ON
t.CustomerKey = f.CustomerKey
where t.CustomerKey=13513
表变量在批处理结束时⾃动被系统删除，所以你不必要像使⽤临时表表⼀样显⽰的对它进⾏删除.
----------------------------------------
另外在今天帮同事Tuning SQL 脚本地时候，发现对于⼤数据量表的查询（10w-100W），⽤变量的⽅式⽐⽤select 的⽅式居然执⾏时间减少了100倍！！似懂⾮懂，但从来没有想到差别如此⼤，惊讶ing，记录⼀笔，研究⼀下
M1：
declare @tempID int
set @tempID =(select lots_id from qs_notes where id='CVT20080321')
select * from ls_Qs_notes where id = @tempID
---返回记录998，⾏执⾏时间6589
M2：
select * from ls_Qs_notes where id =(select lots_id from qs_notes where id='CVT20080321') ---返回记录998 ，⾏执⾏时间60

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。