[转载]SQL 临时表和表变量

[转载]SQL 临时表和表变量 – yxbsmx – 博客园.

通常情况下,表变量中的数据比较少的时候,表变量是存在于内存中的。但当表变量保留的数据较多时，内存中容纳不下，那么它必须在磁盘上有一个位置来存储数据。与临时表类似，表变量是在 tempdb 数据库中创建的。如果有足够的内存，则表变量和临时表都在内存（数据缓存）中创建和处理。

说明:

1) CPU– 事件(SQL语句)使用的 CPU 时间（毫秒）。

2)  Reads–由服务器代表事件读取逻辑磁盘的次数。这些读取操作数包含在语句执行期间读取表和缓冲区的次数。

3) Writes–由服务器代表事件写入物理磁盘的次数。

示例1.变量表

1) 10000条记录

declare @t table
(
id nvarchar(50),
supno nvarchar(50),
eta datetime
)
insert  @t

select top 10000 ID,supno,eta from 表

–cpu :125    reads :13868    writes: 147

–表 ‘#286302EC’。扫描计数 0，逻辑读取 10129 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

–表 ‘表’。扫描计数 1，逻辑读取 955 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

declare @t table
(
id nvarchar(50),
supno nvarchar(50),
eta datetime
)
insert  @t

select top 1000 ID,supno,eta from 表

—    cpu:46    reads:2101     writes:    17
–表 ‘#44FF419A’。扫描计数 0，逻辑读取 1012 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
–表 ‘表’。扫描计数 1，逻辑读取 108 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

–示例2。临时表：

create table #t
(
id nvarchar(50),
supno nvarchar(50),
eta datetime
)
end
insert #t
select top 10000 ID,supno,eta
from 表

–cpu :125    reads:13883       writes:148
–表 ‘#t00000000005’。扫描计数 0，逻辑读取 10129 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
–表 ‘表’。扫描计数 1，逻辑读取 955 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

create table #t
(
id nvarchar(50),
supno nvarchar(50),
eta datetime
)

insert #t
select top 1000 ID,supno,eta
from 表

–cpu: 62    reads: 2095        writes: 17

–表 ‘#t00000000003’。扫描计数 0，逻辑读取 1012 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
–表 ‘表’。扫描计数 1，逻辑读取 108 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

 
–示例3。不创建临时表，直接插入到临时表

select top 10000 ID,supno,eta
into #t
from 表

–cpu:31    reads:1947        writes:83

–表 ‘表’。扫描计数 1，逻辑读取 955 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

select top 1000 ID,supno,eta
into #t
from 表

–cpu: 0    reads: 997        writes:11

–表 ‘表’。扫描计数 1，逻辑读取 108 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

从以上的分析中可以看出,如果使用3)方式,则会少建一个临时表.那么IO中的读写也将减少次数.

1)与2)都会有先建临时表的动作,并进行相应的IO读取操作.

从SQL语句对服务器的cpu使用上来看,第三种情况cpu使用率也相对较低.

从物理写入磁盘操作来看,第三种情况的物理写入次数较少.

在什么情况下使用表变量来代替临时表:

取决于以下三个因素：

在某些情况下，可将一个具有临时表的存储过程拆分为多个较小的存储过程，以便在较小的单元上进行重新编译。

个人建议,当记录行小于1000行的情况下，应尽量使用表变量，除非数据量非常大(大于1000行)并且需要重复使用表。在这种情况下，可以在临时表上创建索引以提高查询性能。但是，各种方案可能互不相同。

Microsoft 建议您做一个测试，来验证表变量对于特定的查询或存储过程是否比临时表更有效。