SQL Server表变量

i.                       什么是表变量

表变量是一种特殊的数据类型，用于存储结果集以进行后续处理。表变量主要用于临时存储一组行，这些行是作为表值函数的结果集返回的。可将函数和变量声明为 table 类型。table 变量可用于函数、存储过程和批处理中。表变量类似于临时表，只是它在内存中存储，而临时表则在磁盘中存储。常常可以用表变量代替游标或者临时表，提高执行效率。

ii.                       表变量的例子

l  Hello world例子：

 

1     declare @userName varchar(100)

2     declare @targettable table

3     (

Num int,

userName varchar(100)

4     )

5     insert into @targettable values(1,'Hello  world')

6     select *  from  @targettable

 

l  表变量代替游标的复杂一点例子（电销系统中的例子）：

 

1   set ANSI_NULLS ON

2   set QUOTED_IDENTIFIER ON

3   go

4   --declareglobal table variable

5   ALTER PROC [dbo].[TargetAssignOpt]

6   @whereClause NVARCHAR(2000),

7   @taskSerialNo INT,

8   @csrName NVARCHAR(50),

9   @csrAssignCountINT,

10  @loginUserID NVARCHAR(50),

11  @AssignBatchNoINT

12  --@AssignCountint output

13  AS

14  BEGIN

15  --declarelocal variables

16   DECLARE @cphonevarchar(50),

17           @ephase int,

18           @luserid varchar(50),

19           @ltime datetime,

20           @cflag int,

21           @fuserid Varchar(100),

22           @MaxRow int,

23           @RowCnt int

24    --set @AssignCount= 0

25   --declare tablevariable

26   declare@targettable table

27   (

28        rownum int IDENTITY(1,1) Primary key NOT NULL,

29        ClientPhone VARCHAR(20) NOT NULL,

30        ExecutePhase SMALLINTNULL,

31        LockUserID VARCHAR(100) NULL,

32        LockTime DATETIMENULL,

33        CloseFlag SMALLINTNULL,

34        FollowUserID VARCHAR(100)

35   )

36  --insertwanted records into table variable

37   INSERT INTO @targettable selecttop(@csrAssignCount) ClientPhone,ExecutePhase, LockUserID, LockTime, CloseFlag, FollowUserID

38   FROM  TM_Task_9_Target

39   whereExecutePhase = 1

40   and ( LockUserID='admin' or exists (Select ManagedUserID from     UM_Subordination_U2U

41                 Where UserID='admin' and ManagedUserID =LockUserID)) orderby newid()

42   

43   set @MaxRow = 0

44   select @MaxRow = count(*) from @targettable

45   --print @MaxRow

46   --print@csrAssignCount

47   

48   set @RowCnt = 1

49  while @RowCnt <= @MaxRow

50  begin

51   if@csrAssignCount = 0 break

52   

53   --get values fromtable variable and assign them to corresponding variable

54   select @cphone = ClientPhone,@ephase = ExecutePhase,@luserid = LockUserID,@ltime = LockTime,

55          @cflag =CloseFlag, @fuserid =FollowUserID

56   from@targettable

57   where rownum = @RowCnt

58   

59   --print @cphone

60   --print @ephase

61   --print @luserid

62   

63   select @RowCnt = @RowCnt + 1

64   

65   --updateTM_Task_#_Target table to mark the record which is allocated

66   UpdateTM_Task_9_Target

67   Set LockUserID = @csrName,

68       LockTime = CONVERT(VARCHAR, getDate(), 120),

69       ExecutePhase =1

70   whereClientPhone = @cphone

71   

72   --insert theallocated record to the table TM_Task_Target_Reassign_Record

73   insert into TM_Task_Target_Reassign_Record(TaskSerialNo,AssignUserID, AssignTime, AssignBatchNo, ClientPhone,

74   OldExecutePhase,NewExecutePhase, OldLockUserID, NewLockUserID,OldLockUserTime, NewLockUserTime, OldCloseFlag,

75   NewCloseFlag,OldFollowUserID, NewFollowUserID)

76   Values(@taskSerialNo,@loginUserID, getDate(), @AssignBatchNo,

77   @cphone,@ephase, 1,@luserid, @csrName,@ltime, getDate(),

78   @cflag, @cflag, @fuserid, @fuserid)

79   

80   set@csrAssignCount = @csrAssignCount - 1

81   

82  end

83  --end

84  END

（经过测试，此处使用表变量，速度上提高不明显。另外，使用表变量时，因为SQL Server 2005不支持传递表参数，而要优化的表名称是不固定的（TM_Task_#_Target）是因此对存储过程的改进很困难。如果用SQL Server 2008，因其支持表参数传递，可以考虑使用表变量替换游标。）

iii.                       表变量的优点

·        A. table 变量的行为类似于局部变量，有明确定义的作用域。这就是在其中声明该变量的函数、存储过程或批处理。在其作用域内table 变量可像常规表那样使用。该变量可应用于 SELECT、INSERT、UPDATE 和 DELETE 语句中用到表或表的表达式的任何地方。但是，table 不能用于以下语句中： select select_list into table_variable

·        在定义 table 变量的函数、存储过程或批处理结束时，会自动清除此变量。

·        table 类型声明中的 CHECK 约束、DEFAULT 值和计算列不能调用用户定义函数。

·        在存储过程中使用 table 变量与使用临时表相比，减少了存储过程的重新编译量。

·        涉及 table 变量的事务只在 table 变量更新期间存在。因此减少了 table 变量对锁定和记录资源的需求。

·        可以在表变量上使用UDF,UDDT,XML。

iv.                       表变量的限制

l  不支持在 table 变量之间进行赋值操作。另外，由于 table 变量作用域有限，并且不是持久数据库的一部分，因而不受transactionrollback的影响。

l  表变量是存储在内存中的，当用户在访问表变量的时候，SQL Server是不产生日志的，而在临时表中是产生日志的;

l   在表变量中，是不允许有非聚集索引的，表变量是不允许有DEFAULT默认值，也不允许有约束。

l  不能显式的创建表变量的索引，也不保留表变量的任何统计信息。在某些情况下，可以通过改用支持索引和统计信息的临时表来改善性能。

l  包含表变量的查询不会生成并行查询执行计划。特大型表变量或者复杂查询中的表变量可能会影响到性能。在这种情况下，请考虑改用临时表

l  理论上说表变量可以@@声明为全局的表变量，但是实际中全局表变量只可系统才拥有。如下所示，先执行step1，再执行step2就会报错：

--step 1

declare @userName varchar(100)

declare @@targettable table

(

Num int,

userName varchar(100)

)

insert into@@targettable values(1,'Hello world')

--- step 2

select *  from  @@targettable

v.                       何时使用表变量

a)     用表变量代替临时表取决于以下因素：

l  表的行数；

l  使用表变量能够减少的重新编译次数；

l  查询的类型和对索引或者统计信息的依赖程度；

l  需要生用UDF,UDDT,XML的时候。

b)     用表变量代替游标，用于存储过程

c)     。。。。。

vi.                       参考文献

1，          SQL  Server2005联机帮助文档

2，          临时表vs.表变量以及它们对SQL Server性能的影响http://www.windbi.com/showtopic-90.aspx

3，          SQL Server 表变量和临时表 http://archive.cnblogs.com/a/2087152/

4，           SQL Server中临时表与表变量的区别http://tech.it168.com/a2009/0304/267/000000267568.shtml