未加星标

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

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二03 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏
问题引入

“菜鸟啊,最近我看到阿里云开发者论坛的数据库RDS中有人在提SQL Server表变量和临时表如何选择的问题,你去深入探讨下这个问题吧,解答解答他们的疑惑吧”,老鸟又开始为菜鸟找活干了。

“鸟哥啊,关于表变量和临时表使用选择的问题啊,向来行业里争论不休,我比较担心我们的观点被人家拍砖啊”。

“鸟啊,有争论才说明这个问题有价值啊,所以我们才更应该去弄清楚,道明白啊”。反正老鸟总会找到合适的理由。

“那好吧,要把这个问题要刨根问底,我们需要分四篇文章来把这个问题理清楚。”,菜鸟掰着手指头就数了出来:

表变量和临时表基本概念

表变量和临时表的对比

表变量和临时表认知误区

表变量和临时表的选择

什么是表变量

关于什么是SQL Server的表变量,我们分别从表变量的定义、表变量的作用和表变量的使用三个角度来看看什么是表变量。

表变量定义

表变量,是微软至SQL Server 2000以来引入的概念,从名称我们就可以很容易看出,表变量本质是一个变量,只是它具有了正式表对象的很多属性。比如:它有表字段、字段数据类型、字段宽度、主键、唯一约束、NULL、NOT NULL约束、CHECK和DEFAULT约束。但是,表变量不支持约束命名,不支持索引,不支持外键,不支持表变量定义后的任何表变量结构的修改,仅可做数据的DML操作。

表变量的作用

当我们需要在当前会话临时缓存少量的中间数据结果集,供当前会话多次使用这同一数据集或者同一数据结果集的一部分时,我们可以考虑使用表变量,表变量中的数据是缓存在内存中(大部分情况下如此,也有极少情况例外,我们后面的文章会讲到)。注意这里是少量数据集,不是大量结果集,如果非要给一个参照经验值的话,个人建议是最好不要超过10万条数据记录,所占的空间大小不要超过100MB。

表变量的使用

关于表变量作用,在此我们以一个例子来说明。在这个例子中,我们定义了一个表变量来暂时存放商品的基本属性信息,然后INSERT了三条数据,紧接着对其中一条数据做UPDATE操作,再接着DELETE了一条数据,最后我们SELECT了整个表变量存放的数据。

USE tempdb GO 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()) ); INSERT INTO @tb_table(ProductName, Length, Windth, Height) VALUES('A', 0.1, 0.2, 0.3); INSERT INTO @tb_table(ProductName, Length, Windth, Height) VALUES('B', 0.4, 0.5, 0.6); INSERT INTO @tb_table(ProductName, Length, Windth, Height) VALUES('C', 0.7, 0.8, 0.9); UPDATE A SET Length = 2.5 FROM @tb_table AS A WHERE RowID = 1 ; DELETE TOP(1) A FROM @tb_table AS A WHERE RowID = 2; SELECT * FROM @tb_table;

从这个例子,我们看到了表变量所具有的正式表对象的属性,表变量是如何定义的,以及DML操作,在当前会话结束后,表变量会被SQL Server自动回收。

这里需要特别提醒下,SQL Server系统不允许我们像正式表对象那样对约束进行显示命名,SQL Server会报告错误。比如,定义表变量代码:

USE tempdb GO DECLARE @tb_table TABLE( RowID INT IDENTITY(1,1) NOT NULL PRIMARY KEY ,ProductName NVARCHAR(50) NOT NULL UNIQUE ,Length DECIMAL(8,2) NOT NULL ,Windth DECIMAL(8,2) NOT NULL ,Height DECIMAL(8,2) NOT NULL ,Indate DATETIME NOT NULL CONSTRAINT DF_tbTable DEFAULT(GETDATE()) ,CONSTRAINT CK_Windth CHECK(Windth>0.0) );

报错信息如下:

Msg 156, Level 15, State 1, Line 10 Incorrect syntax near the keyword 'CONSTRAINT'. 什么是临时表

在看完什么是表变量以后,我们还是分别从临时表定义、临时表的作用和临时表的使用三个角度来看看什么是SQL Server的临时表。

临时表定义

SQL Server的临时表是一种特殊的表,表名字是以#或者##打头。无论临时表在哪个数据库下创建,SQL Server均把临时表结构信息和数据存储在Tempdb数据库下。

以#打头的临时表称为局部临时表,这种类型的临时表仅当前进程可见,其他进程不可访问,生命周期会随着当前连接进程的关闭而消亡。

以##打头的临时表称为全局,此类型的临时表对所有进程可见,当前进程和其他进程均可访问,生命周期是所有使用到全局临时表的连接完全关闭后,临时表消亡。

临时表的作用

临时表的作用和表变量类似,均是用于暂时缓存数据。临时表中的数据会被储存在Tempdb的物理文件磁盘上,当需要数据读取时,SQL Server会将临时表中数据从磁盘文件读入SQL Server Buffer Pool中,然后返回给客户端。因此,临时表对数据的存储和读取会有物理的IO Write和IO Read的。临时表相较于表变量可以存储稍微大量一些的数据,比如数据量超过10万条记录数,数据空间占用量超过100MB。但是,如果经常有类似的临时表使用场景时,建议对Tempdb数据库做性能优化相关的配置工作。

临时表的使用

为了和表变量形成对比,我特意将表结构和数据保持一致,不同的地方在于,我们可以对约束进行显示指定命名,可以创建索引。在次,为了看清楚局部临时表和全局临时表的区别,我们也创建了一个全局临时表。

USE tempdb GO IF OBJECT_ID('tempdb..#tb_table','U') IS NOT NULL DROP TABLE #tb_table GO CREATE TABLE #tb_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 ,Height DECIMAL(4,2) NOT NULL CHECK(Height>0.0) ,Dimension AS (Length * Windth * Height) ,Indate DATETIME NOT NULL CONSTRAINT DF_tbTable DEFAULT(GETDATE()) ,CONSTRAINT CK_Windth CHECK(Windth>0.0) ); CREATE INDEX IX_ProductName ON #tb_table(ProductName); GO INSERT INTO #tb_table(ProductName, Length, Windth, Height) VALUES('A', 0.1, 0.2, 0.3); INSERT INTO #tb_table(ProductName, Length, Windth, Height) VALUES('B', 0.4, 0.5, 0.6); INSERT INTO #tb_table(ProductName, Length, Windth, Height) VALUES('C', 0.7, 0.8, 0.9); IF OBJECT_ID('tempdb..##tb_table','U') IS NOT NULL DROP TABLE ##tb_table GO SELECT * INTO ##tb_table FROM #tb_table; UPDATE A SET Length = 2.5 FROM #tb_table AS A WHERE RowID = 1 ; DELETE TOP(1) A FROM #tb_table AS A WHERE RowID = 2; SELECT * FROM #tb_table; SELECT * FROM ##tb_table

执行上面的局部临时表和全局临时表创建语句之后,我们在SSMS中新开启一个连接,执行下面的语句:

SELECT * FROM ##tb_table GO SELECT * FROM #tb_table

返回执行结果如下:


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

返回执行消息如下:


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

从返回的结果分析可知:局部临时表仅当前连接可以访问,对其他进程不可见(访问报告对象不存在的错误),而全局临时表不仅当前连接可以访问,对其他进程可见。

写在最后

关于SQL Server表变量和临时表的使用规则是一个仁者见仁智者见智的话题,所以我们希望能够把这个话题尽可能的剖析清楚,让读者对两者有非常清楚的认识。

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

分页:12
转载请注明
本文标题:SQL Server 表变量和临时表系列之概念篇
本站链接:http://www.codesec.net/view/521554.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(79)