(转)本地临时表（#tbl）和表变量（@tbl）的区别

最新推荐文章于 2022-05-28 11:00:38 发布

哈罗北鼻1223

最新推荐文章于 2022-05-28 11:00:38 发布

阅读量842

点赞数

分类专栏： Sql server

Sql server 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

本地临时表（#tbl）

CREATE TABLE #News
(
　　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', GETDATE())
SELECT News_id, NewsTitle, NewsContent, NewsDateTime FROM dbo.#News
DROP TABLE #News

查询结果：

表变量（@tbl）

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', GETDATE())
SELECT News_id, NewsTitle, NewsContent, NewsDateTime FROM @News

查询结果：

效果是相同的。

分析：

我们可以看到上面两种情况实现的是一样的效果。第一种，临时表被创建后，如果在执行的时候，没有DROP Table的操作，第二次就不能再被创建，而定义表变量则不需要进行DROP Table的操作，一次执行完成后就会消失。

临时表的作用域大点，可以被服务器上其它连接访问，在SQL服务重启时才会自动删除。
表变量只能被创建者访问，在当前存储过程内有效，存储过程返回和结束时自动销毁。

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