关闭

SQL Server 临时表和表变量系列之认知误区篇

244人阅读 评论(0) 收藏 举报
分类:
原文:点击打开链接

摘要

关于临时表和表变量,是一个老生常谈的话题,但是,我相信很多SQL Server老司机都存在或多或少的认知误区。指出一些常见的认知误区就是写作本文的目的,希望以此来找到一些常常被我们忽略的地方。

认知误区

SQL Server关于临时表和表变量的常见的认知误区包含以下六点:
表变量不支持事务
表变量不能创建索引
表变量没有统计信息
表变量存驻留在内存中
表变量访问比临时表快
局部临时表不需要手动回收资源

表变量不支持事务

从我们前一篇文章SQL Server 临时表和变量系列之对比篇中的“对事务支持”部分的测验来看,的确表变量是不支持用户事务回滚的。但是,以此得出表变量不支持事务这种说法是错误的。原因是:表变量的确不支持用户显示事务回滚,即有BEGIN TRANSACTION类型的事务回滚,但是表变量还是支持DML语言操作的事务性的。
怎么理解呢?就是说,表变量数据操作的时候,是不可能存在一些数据成功,一些数据失败的。比如:在一个批处理中,向表变量中插入两条记录,不可能存在只有一条记录插入成功,而另一条记录插入失败的情况;只可能是两条数据都插入成功或者都不成功的情况。这就是表变量支持事务原子性的一种表现。

--Table variable support transaction
-- declare temp variable
DECLARE
    @tb_table TABLE(
RowID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
,ProductName NVARCHAR(50) NOT NULL UNIQUE
);

INSERT INTO @tb_table
SELECT NEWID() UNION ALL SELECT NEWID();

--END

表变量不能创建索引

表变量本质上是一种特殊的变量类型,只不过它具有了很多表的属性。比如:字段、字段数据类型、字段宽度、唯一约束、NULL、NOT NULL约束、CHECK和DEFAULT约束等。由于表变量定义完毕以后,不支持对表变量结构的任何变更和索引创建,所以很多人会认为表变量不能创建索引,比如这篇文章SQL Server Temp Table vs Table Variable Performance Testing中的这句话“However, when we query rows using the indexed column of the temporary table, which is not indexed in the table variable since this is not available for table variables”就认为表变量无法创建索引,这个观点的错误,导致了整个测试结论是错误的。其实,表变量完全可以在定义的时候就直接创建索引,参见如下代码,在定义表变量时候创建了非聚集索引IX_Indate:

--Table variable support Index
DECLARE
    @tb_table TABLE(
RowID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
,ProductName NVARCHAR(50) NOT NULL UNIQUE
,Length DECIMAL(4,2) NOT NULL CHECK(Length>0.0)
,Windth DECIMAL(4,2) NOT NULL CHECK(Windth>0.0)
,Height DECIMAL(4,2) NOT NULL CHECK(Height>0.0)
,Dimension AS (Length * Windth * Height)
,Indate DATETIME NOT NULL  DEFAULT(GETDATE())
,index IX_Indate NONCLUSTERED (Indate)
);
--END

表变量没有统计信息

在关系型数据库RDBMS系统中,统计信息是执行计划正确评估的基础,和SQL执行效率息息相关,对SQL语句的查询性能起着至关重要的作用。关于SQL Server统计信息的详情,请参见之前的文章SQL Server幕后英雄 - 统计信息。那么,表变量到底有没有统计信息呢?在过往的经验中,很多SQL Server数据库的同行都认为表变量没有统计信息存在,其实这种认识是错误的。我们可以参加下面的例子:

--Table variable has statistics
DECLARE
    @tb_table TABLE(
RowID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
,ProductName NVARCHAR(50) NOT NULL UNIQUE
,Length DECIMAL(4,2) NOT NULL CHECK(Length>0.0)
,Windth DECIMAL(4,2) NOT NULL CHECK(Windth>0.0)
,Height DECIMAL(4,2) NOT NULL CHECK(Height>0.0)
,Dimension AS (Length * Windth * Height)
,Indate DATETIME NOT NULL  DEFAULT(GETDATE())
,index IX_Indate NONCLUSTERED (Indate)
);

DECLARE
    @table_variable_id bigint
    ;
SELECT TOP 1 @table_variable_id = object_id
FROM tempdb.sys.all_objects AS A
WHERE parent_object_id = 0
ORDER BY create_date DESC

SELECT  
    statistics_name = st.name
    ,table_name = OBJECT_NAME(st.object_id)
    ,column_name = COL_NAME(stc.object_id, stc.column_id)
FROM    sys.stats AS st WITH(NOLOCK) 
        INNER JOIN sys.stats_columns AS stc WITH(NOLOCK)
            ON st.object_id = stc.object_id  
            AND st.stats_id = stc.stats_id 
WHERE st.object_id = @table_variable_id
--END

执行完毕后,表变量的统计信息展示如下:
01.png

表变量仅驻留在内存中

我在一些图书或者网络上的文章经常看到有人讲,表变量的数据是驻留在内存中的。这个观点在大部分情况下是正确的,但是当SQL Server认为数据库内存不足时(不是物理机内存不足,是SQL OS的内存不足),表变量的数据是会写入到Tempdb的数据文件,即写入了磁盘文件中。
我们可以参照下面的思路来证明SQL Server内存不足时,表变量的数据会写入tempdb数据文件:
修改Max Server Memory为512MB
重启SQL Service使得配置生效
获取测试前的内存使用量和Tempdb数据文件大小
向表变量中存入10000条记录(大概会占据780MB空间,超过512MB的最大内存大小)
再次获取测试后的内存使用量和Tempdb数据文件大小
代码如下:

USE tempdb
GO

--table variable just saved data into memory

-- change configuration of Max Server Memory to 512MB.
EXEC sys.sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure 'max server memory (MB)', 512
GO
RECONFIGURE WITH OVERRIDE
GO

-- restart SQL Server serivces

-- if you didn't restart sql service, clean the buffer pool
CHECKPOINT
GO
DBCC DROPCLEANBUFFERS

-- Check memory used before testing
SELECT MemoryUsedBefore = count(1) / 128.
FROM sys.dm_os_buffer_descriptors b 
WHERE is_modified = 1

-- Check disk space used before testing
SELECT
        [DBName] = db_name()
        , [LogName] = s.name
        , [SpaceUsedBefore] = CAST(FILEPROPERTY(s.name, 'SpaceUsed') AS float)* CONVERT(float,8)/1024
FROM sys.database_files AS s

-- table variable data inserting area
DECLARE  @saveToDisk TABLE(bigChar char(8000));
DECLARE
    @do int = 1; 
WHILE @do <= 100000
BEGIN
    INSERT INTO @saveToDisk VALUES(REPLICATE('A', 8000));
    SET @do = @do + 1;
END

-- Check memory used after testing
SELECT MemoryUsedAfter = count(1) / 128.
FROM sys.dm_os_buffer_descriptors b 
WHERE is_modified = 1

-- Check disk space used after testing
SELECT
        [DBName] = db_name()
        , [LogName] = s.name
        , [SpaceUsedAfter] = CAST(FILEPROPERTY(s.name, 'SpaceUsed') AS float)* CONVERT(float,8)/1024
FROM sys.database_files AS s

执行结果截图如下:
02.png

分析结果:
从结果来看Tempdb数据文件从2.4MB增长到783.7MB,大约增加了780MB,这个数据增长量和表变量需要暂存的数据量大小是非常相近的;内存大小从0.3MB增长到250.6MB,大约增长了250MB,接近SQL Server Max Server Memory的一半。这个现象已经可以说明当SQL Server最大内存大小不足以存放表变量的数据大小的时候,SQL Server会使用tempdb数据文件来暂存表变量的数据;还可以推测,当表变量存放数据接近SQL Server Max Server Memory一半空间的时候,就会将表变量数据写入磁盘。
从Windows资源管理器层面我们也可以发现sqlservr进程有大量写入Tempdb数据文件的操作。如下截图,在执行表变量插入操作的时候,Disk Write速度接近了100MB/Sec,平时的速度不到100KB/Sec:
03.png

表变量访问比临时表快

由之前关于临时表和表变量系列文章SQL Server 临时表和变量系列之对比篇中“数据存储”部分结论,我们知道表变量的数据是存放在SQL Server的内存缓存中,而临时表是存放在Tempdb数据文件的磁盘上,因此很多人会得出结论,使用表变量比临时表效率更高,性能更好。这种认知也是不够全面和错误的,理由是表变量数据也有可能存在磁盘中:
本文的前一小节“表变量仅驻留在内存中”,我们已经使用实际例子证明了,当SQL Server内存大小不足以存放表变量数据大小的时候,SQL Server会将表变量的数据全部存放在Tempdb的数据文件磁盘上,以此来避免因为内存不足而导致服务死掉。换句话说,当这种情况出现的时候(当然这种场景在现在大内存数据库时代已经非常少见了),表变量和临时表都是将数据暂存在磁盘上,数据存取都必须有物理磁盘的读写操作,无所谓谁快谁慢。基于大多数人习惯于不给表变量创建索引,这种场景反而会导致表变量的查询比临时表慢,性能更低。

局部临时表不需要手动回收资源

由之前关于临时表和表变量系列文章SQL Server 临时表和变量系列之对比篇中“析构方式”部分,我们知道局部临时表在当前进程退出的时候,SQL Server系统会自动析构局部临时表对象。因此,很多人认为我们没必要再手动回收临时表资源。
但是,我个人还是强烈建议手动回收临时表资源,又特别是临时表中暂存了大量数据的时候(比如超过10万条记录数),使用TRUNCATE TABLE不记录日志的方式删除临时表所有数据,然后再手动删除临时表结构。以防止临时表直接DROP操作给tempdb日志文件带来写入压力。

IF OBJECT_ID('tempdb..#tb_table','U') IS NOT NULL
BEGIN
    TRUNCATE TABLE #tb_table
    DROP TABLE #tb_table
END

写在最后

这篇文章主要谈论了人们常见的关于临时表和表变量的认知误区,希望能带给大家关于两者的正确认识,也为下一篇文章“SQL Server 临时表和表变量系列之选择篇”打下基础。

0
0
查看评论

SQL Server 表变量和临时表系列之概念篇

问题引入 “菜鸟啊,最近我看到阿里云开发者论坛的数据库RDS中有人在提SQL Server表变量和临时表如何选择的问题,你去深入探讨下这个问题吧,解答解答他们的疑惑吧”,老鸟又开始为菜鸟找活干了。 “鸟哥啊,关于表变量和临时表使用选择的问题啊,向来行业里争论不休,我比较担心我们的观点被人家拍...
  • lishimin1012
  • lishimin1012
  • 2017-01-06 14:37
  • 544

SQL Server 表变量与临时表区别

问题 1:为什么在已经有了临时表的情况下还要引入表变量?解答 1:与临时表相比,表变量具有下列优点: • 如 SQL Server 联机丛书“表”(Table) 一文中所述,表变量(如局部变量)具有明确定义的范围,在该范围结束时会自...
  • lovehongyun
  • lovehongyun
  • 2008-01-09 09:27
  • 10990

SQL Server临时表和表变量

文章主要描述的是SQL Server临时表和表变量在实际操作中的区别介绍,我们在数据库中实际操作中,对于表的使用的时候,一般都会使用两种使用表的方法,分别就是使用临时表及表变量。在实际使用的时候,我们如何灵活的在存储过程中运用它们。虽然它们实现的功能基本上是一样的,可如何在一个存
  • andycode
  • andycode
  • 2011-07-25 11:09
  • 407

SQL server 临时表和表变量

临时表<br />临时表与永久表相似,只是它的创建是在Tempdb中,它只有在一个数据库连接结束后或者由SQL命令DROP掉,才会消失,否则就会一直存在。临时表在创建的时候都会产生SQL Server的系统日志,虽它们在Tempdb中体现,是分配在内存中的,它们也支持物理的磁盘,但用户在...
  • misol
  • misol
  • 2010-12-15 09:25
  • 616

sqlserver 中临时表、临时变量和with as关键词创建“临时表”的区别

SQL数据库中数据处理时,有时候需要建立临时表,将查询后的结果集放到临时表中,然后在针对这个数据进行操作。   创建“临时表”(逻辑上的临时表,可能不一定是数据库的)的方法有一下几种:   1.with tempTableName as方法(05之后出现):   with temp...
  • miqi770
  • miqi770
  • 2016-05-26 15:39
  • 3956

SQL SERVER临时表的使用&表变量的使用

临时表: 创建临时表可以创建本地和全局临时表。本地临时表仅在当前会话中可见;全局临时表在所有会话中都可见。 本地临时表的名称前面有一个编号符 (#table_name),而全局临时表的名称前面有两个编号符 (##table_name)。
  • S630730701
  • S630730701
  • 2016-08-03 16:57
  • 2449

sql 临时表和表变量

临时表内涵:以#开头的局部临时表,以##开头的全局临时表。存储:存放在tempdb数据库(包含 局部临时表,全局临时表)。作用域: 局部临时表:对当前连接有效,只在创建它的存储过度、批处理、动态语句中有效,类似于C语言中局部变量的作用域。 全局临时表:在所有连接对它都结束引用时,会被删除,对创建者来...
  • MoDun_N
  • MoDun_N
  • 2017-09-15 19:17
  • 110

比较表变量和临时表

1、回滚事务对表变量无效,回滚事务对临时表有效SET NOCOUNT ON; DECLARE @TestTable TABLE ( RowID INT IDENTITY PRIMARY KEY CLUSTERED, Name VARCHAR(9) NOT NULL UNIQUE...
  • roy_88
  • roy_88
  • 2013-01-03 19:13
  • 3996

再议SQL Server临时表和表变量

今天在我和一家软件公司的开发人员讨论数据库设计调优的时候又讨论到了表变量和临时表的问题,觉得这个问题确实是一个争议比较大的问题  今天在我和一家软件公司的开发人员讨论数据库设计调优的时候又讨论到了表变量和临时表的问题,觉得这个问题确实是一个争议比较大的问题。  其实从上次发表了表变量和临时表的一个帖...
  • plean
  • plean
  • 2008-09-10 16:34
  • 322

SQL Server中的临时表和表变量

在SQL Server的性能调优中,有一个不可比拟的问题:那就是如何在一段需要长时间的代码或被频繁调用的代码中处理临时数据集?表变量和临时表是两种选择。   在SQL Server的性能调优中,有一个不可比拟的问题:那就是如何在一段需要长时间的代码或被频繁调用的代码中处理临时数据集?表...
  • socoldinxian
  • socoldinxian
  • 2013-08-30 17:36
  • 516
    个人资料
    • 访问:2007133次
    • 积分:27722
    • 等级:
    • 排名:第236名
    • 原创:796篇
    • 转载:314篇
    • 译文:1篇
    • 评论:197条