表变量和临时表的一些相关

 

表变量：  

  DECLARE @tb  table(id   int   identity(1,1), name   varchar(100))   

  

  INSERT @tb   

 SELECT id, name  FROM mytable   WHERE name like ‘zhang%’ 

 

临时表： 

  SELECT name, address

  INTO #ta   FROM mytable  

  WHERE name like ‘zhang%’

（if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#ta') and type='U')

   drop table #ta）

 

1 . 为什么要使用表变量

 

表变量是从2000开始引入的，微软认为与本地临时表相比，表变量具有如下优点：

  a.与其他变量的定义一样，表变量具有良好的定义范围，并会被自动清除；
  b.在存储过程中使用表变量会减少存储过程重新编译的发生；
  c.表变量需要更少的锁请求和日志资源；
  d.可以在表变量上使用UDF,UDDT,XML。

2 .表变量的限制

与临时表相比，表变量存在着如下缺点：
  a.在表变量上没有统计信息，查询优化器根据固定的预估值来选择执行计划，在数据很多的情况下，会导致查询优化器选择很差的执行计划；
  b.不能直接在表变量上创建索引，但可以通过创建约束（主键、唯一）来建立索引；
  c.在DECLARE后，不能再对表变量进行更改；
  d.不能对表变量执行INSERT EXEC , SELECT INTO语句（只针对05前的版本）；
  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
*/ 

From：http://www.cnblogs.com/luolongda/archive/2010/12/22/1913405.html

 

拓展：

判断临时表是否存在

Way 1

if(exists(select name from tempdb..sysobjects where name like'%temptab%' and type='U'))
   drop table #temptab

Way 2

if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#tempcitys') and type='U')
   drop table #tempcitys

Way 3

IF OBJECT_ID('tempdb..#') IS NOT NULL
   DROP TABLE #

OBJECT_ID此函数返回数据库对象标识号

 

判断数据库里有没有存在PerPersonData这样一张表

if exists (select * from sysobjects where objectproperty(object_id('PerPersonData'),'istable') = 1)

OBJECTPROPERTY：返回当前数据库中对象的有关信息。1表“真”。同样可以写成OBJECTPROPERTY(id, isUserTable) = 1

if exists (select * from sysobjects where id = object_id(N'PerPersonData') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
drop table 'PerPersonData'

 

判断试图是否存在
if exists (select * from sysobjects where id = object_id(N‘[dbo].[ESTMP]‘)
and OBJECTPROPERTY(id, N‘IsView‘) = 1)
  drop view ESTMP

From:http://blog.csdn.net/leamonjxl/article/details/6603007