表变量与临时表

CREATE   proc   usp_kqmj
@st_date   datetime,
@en_date   datetime
as  
select   *     into   #tyskq   from   fpc_workrecord   where   rdate   between   @st_date   and   @en_date
update   #tyskq

set   macno=case   when   rtime   between   '1900-1-1   08:30:00 '   and   '1900-1-1   11:00:00 '
or   (rtime   between   '1900-1-1   13:30:00 '   and   '1900-1-1   17:30:00 ')   then   'mj '   else   '0010 '   end

create   table   #taa   (rq   datetime   null,rno   varchar(10)   null,rname   varchar(12)   null,depname   varchar(20)   null,
jcsj   datetime   null,memo   varchar(50)   null,startd   datetime   null,endd   datetime   null)

select   rdate   as   rq,rno,[name]   as   rname,depname,rtime   as   jcsj   into   #tempaa   from   #tyskq
where   macno   like   'mj% '

insert   into   #taa   (rq,rno,rname,depname,jcsj)   select   rq,rno,rname,depname,jcsj   from   #tempaa
update   #taa
set   startd=@st_date
set   endd=@en_date
select   *   from   #taa   order   by   rq,rno
drop   table   #tempaa
drop   table   #taa
drop   table   #tyskq
GO
上面是存储过程中用到临时表，临时表与永久表相似，但临时表存储在   tempdb   中，当不再使用时会自动删除。

有本地和全局两种类型的临时表，二者在名称、可见性和可用性上均不相同。本地临时表的名称以单个数字符号   (#)   打头；它们仅对当前的用户连接是可见的；当用户从   Microsoft®   SQL   Server™   2000   实例断开连接时被删除。全局临时表的名称以数学符号   (##)   打头，创建后对任何用户都是可见的，当所有引用该表的用户从   SQL   Server   断开连接时被删除。

使用临时表会导致存储过程的重编译 所以请尽量使用表变量，上述存储过程改成表变量如下：

CREATE   proc   usp_kqmj
@st_date   datetime,
@en_date   datetime
as  
begin
        select   *     into   #tyskq   from   fpc_workrecord   where   rdate   between   @st_date   and   @en_date
       
        update   #tyskq
        set  
                macno=case   when   (rtime   between   '1900-1-1   08:30:00 '   and   '1900-1-1   11:00:00 ')
                            or   (rtime   between   '1900-1-1   13:30:00 '   and   '1900-1-1   17:30:00 ')   then   'mj '   else   '0010 '   end
       
        declare   @taa   table(
        rq             datetime   null,
        rno           varchar(10)   null,
        rname       varchar(12)   null,
        depname   varchar(20)   null,
        jcsj         datetime   null,
        memo         varchar(50)   null,
        startd     datetime   null,e
        ndd           datetime   null)
       
        select  
                rdate   as   rq,rno,[name]   as   rname,depname,rtime   as   jcsj  
        into   #tempaa  
        from  
                #tyskq
        where  
                macno   like   'mj% '
       
        insert   into   @taa(rq,rno,rname,depname,jcsj)  
        select   rq,rno,rname,depname,jcsj   from   #tempaa
       
        update   @taa
        set  
                startd=@st_date,
                endd     =@en_date
       
        select   *   from   @taa   order   by   rq,rno
end
GO

 

在开发过程中，经常会遇到使用表变量和本地临时表的情况。下面是对二者的一个介绍：

1. 为什么要使用表变量

表变量是从2000开始引入的，微软认为与本地临时表相比，表变量具有如下优点：
  a.与其他变量的定义一样，表变量具有良好的定义范围，并会被自动清除；
  b.在存储过程中使用表变量会减少存储过程重新编译的发生；
  c.表变量需要更少的锁请求和日志资源；
  d.可以在表变量上使用UDF,UDDT,XML。

2.表变量的限制

与临时表相比，表变量存在着如下缺点：
  a.在表变量上没有统计信息，查询优化器根据固定的预估值来选择执行计划，在数据很多的情况下，会导致查询优化器选择很差的执行计划；
  b.不能直接在表变量上创建索引，但可以通过创建约束（主键、唯一）来建立索引；
  c.在DECLARE后，不能再对表变量进行更改；
  d.不能对表变量执行INSERT EXEC,SELECT INTO语句；
  e.不能通过EXEC或sp_executesql来执行牵涉到表变量的动态SQL语句，但如果表变量是在动态SQL语句内定义的，则可以。

3.那什么时候可以使用表变量

要使用表变量应该根据如下规则来判断：
  a.表的行数；
  b.使用表变量能够减少的重新编译次数；
  c.查询的类型和对索引或者统计信息的依赖程度；
  d.需要生用UDF,UDDT,XML的时候。
其实也就说，得从实际出发，根据具体的查询，作出具体的选择。但是，其中很关键的一点，如果表的行数非常多，使用表变量其实是更费资源的。有人提出了这样的建议：对于行数较少的情况下(小于1000行）可以使用表变量；如果行数很多（有几万行），则使用临时表。

因此，在实际的开发中，应通过分别使用临时表或表变量进行对比后，才作出决定。


下面是一个例子，插入临时表和表变量的数据有20多万行，可以看到，使用临时表的时间是使用表变量所花时间的1/5。

表 'SalesOrderHeader'。扫描计数 3，逻辑读取 130 次，物理读取 9 次，预读 43 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 '#SalesOrderDetail___________________________________________________________________________________________________00000000001F'。扫描计数 3，逻辑读取 12331 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

SQL Server 执行时间:
   CPU 时间 = 2281 毫秒，占用时间 = 19726 毫秒。
select with temporary table: 20140 ms

********************************************************************************

表 'SalesOrderHeader'。扫描计数 0，逻辑读取 764850 次，物理读取 17 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 '#4E88ABD4'。扫描计数 1，逻辑读取 12331 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

SQL Server 执行时间:
   CPU 时间 = 4375 毫秒，占用时间 = 107160 毫秒。
select with table variable: 107160 ms

4.使用表变量的误区

对于表变量，很多人认为，表变量和其他变量一样，只存在内存中，其实这是不正确的，表变量也存在tempdb中。可以通过下面例子进行对比。

CREATE TABLE #TempTable (TT_Col1 INT)

DECLARE @TableVariable TABLE (TV_Col1 INT)

SELECT TOP 2 *

FROM tempdb.sys.objects

ORDER BY create_date DESC


name
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
#03317E3D
#TempTable__________________________________________________________________________________________________________000000000003


#03317E3D就是刚创建的表变量；

5.其他

  表变量不受rollback影响，某些情况下会破坏数据的完整性。

CREATE TABLE #TempTable (TT_Col1 INT)
DECLARE @TableVariable TABLE (TV_Col1 INT)
INSERT #TempTable VALUES (1)
INSERT @TableVariable VALUES (1)
BEGIN TRANSACTION
     INSERT #TempTable VALUES (2)
      INSERT @TableVariable VALUES (2)
ROLLBACK
SELECT * FROM #TempTable

/*
TT_Col1
-------
1
*/

SELECT * FROM @TableVariable
--返回了两条记录
/*
TV_Col1
-------
1
2
*/



6.参考

http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k
www.sqlservercentral.com/articles/63472/databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html
www.sqlservercentral.com/articles/Temporary+Tables/66720/