表变量 和临时表简介

本文导读：定义一个sql server表变量的方法和定义一个用户自定义表的方法是一样的；然而，定义了一个表变量之后，你就不能再改变该表的定义了。而且你必须在定义这个表变量的时候给出它所有属性的定义.

定义一个sql server表格变量的方法和定义一个用户自定义表格的方法是一样的；例如

1.

DECLARE @T_ChartsIdeaItem1 Table(XData char(20) null,YData char(20) null)

INSERT INTO @T_ChartsIdeaItem1(XData,YData)VALUES ('BU9876', 'Creating')

select * from @T_ChartsIdeaItem1

2.

DECLARE@News table

(

News_id int NOT NULL,

NewsTitle varchar(100)，

NewsContent varchar(2000)，

NewsDateTime datetime

)

INSERT INTO @News (News_id, NewsTitle, NewsContent,NewsDateTime)

VALUES (1,'BlueGreen', 'Austen', 200801, GETDATE())

SELECT News_id, NewsTitle, NewsContent,NewsDateTime FROM @News

不是所有的INSERT命令都可以对表格变量执行操作。比如，当你向一个表格变量插入变量值时，你就不能用EXEC命令。通过执行一个已保存的程序或是一系列命令来为一个表格变量插入变量值将会返回错误的结果。而且，也不是所有的SELECT命令都可以对表格变量执行操作，你不能用SELECT INTO命令来为一个表格变量插入数据。

对于sql server表格变量，你不能使用某些系统程序，比如sp_depends。运行时产生的并被随机命名的表格变量的对象名被存储在tempdb数据库中。由于大多数系统功能和系统程序需要对象名和标识代码，所以对一个表格变量执行系统功能和系统程序就是一件很困难的事。为了对一个表格变量执行系统功能和系统程序，我们需要先进行一些复杂的调整以获取该表格变量的对象名和/或标识代码。

一个表格变量的有效范围就是整个批处理，程序，或函数的处理过程。当在一段程序中发出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行）可以使用表变量；如果行数很多（有几万行），则使用临时表。

 

4、使用表变量的误区

对于表变量，很多人认为，表变量和其他变量一样，只存在内存中，其实这是不正确的，表变量也存在tempdb中。

临时表：

代码示例:

CREATETABLE dbo.#News

(

News_id int NOT NULL,

NewsTitle varchar(100)，

NewsContent varchar(2000)，

NewsDateTime datetime

)

INSERT INTO dbo.#News (News_id, NewsTitle,NewsContent, NewsDateTime)

VALUES (1,'BlueGreen', 'Austen', 200801, GETDATE())

SELECT News_id, NewsTitle, NewsContent,NewsDateTime FROM dbo.#News

DROP TABLE dbo.[#News]

比较临时表及表变量都可以通过SQL的选择、插入、更新及删除语句，它们的的不同主要体现在以下这些：

1)当用户在访问表变量时，SQLServer是不产生日志的，而在临时表中是产生日志的;

2)在表变量中，是不允许有非聚集索引的;

3)表变量是不允许有DEFAULT默认值，也不允许有约束;

4)临时表上的统计信息是健全而可靠的，但是表变量上的统计信息是不可靠的;

5)临时表中是有锁的机制，而表变量中就没有锁的机制。