• 售前

  • 售后

热门帖子
入门百科

sqlserver 临时表 Vs 表变量 具体介绍

[复制链接]
风吹吹蛋蛋疼风w 显示全部楼层 发表于 2021-10-26 14:16:55 |阅读模式 打印 上一主题 下一主题
这里我们在SQL Server 2005\SQL Server 2008版本上通过举例子,说明暂时表和表变量两者的一些特征,让我们对暂时表和表变量有进一步的熟悉。在本章中,我们将从下面几个方面去举行描述,对其中的一些特征举例子说明:
束缚(Constraint) 索引(Index) I/0开销 作用域(scope) 存儲位置 其他  
例子描述
<HR>束缚(Constraint)
           在暂时表和表变量,都可以创建Constraint。针对表变量,只有定义时能加Constraint。
e.g.在Microsoft SQL Server Management Studio(MSSMS)查询中,创建暂时表并建Constraint场景,<脚本S1.>
[code]Use tempdb
go
if object_id('Tempdb..#1') Is Not Null
Drop Table #1
Go
Create Table #1
(
ID int,
Nr nvarchar(50) not null,
OperationTime datetime default (getdate()),
Constraint PK_#1_ID Primary Key (ID)
)

Alter Table #1 Add Constraint CK_#1_Nr Check(Nr Between '10001' And '19999')
Go

< 脚本S1.>中,可以看出在暂时表#1的创建时,创建Constraint如“Constraint PK_#1_ID Primary Key(ID)”,也可以在创建暂时表#1后创建Constraint,如“Alter Table #1 Add Constraint CK_#1_Nr Check(Nr Between '10001' And'19999')”,下面我们来看表变量的场景,在定义表变量时不能指定Constraint名,定义表变量后不能对表变量创建Constraint。
e.g. 在定义表变量时不能指定Constraint名<代码S2.>
[code]Use tempdb
Go
Declare @1 Table
(
ID int,
Nr nvarchar(50) not null,
OperationTime datetime default (getdate()),
Constraint [PK_@1_ID] Primary Key (ID)
)

[img=798 border=0,281 alt=image src=]https://www.caogenba.net/[/img]
在定义表变量后不能对表变量创建Constraint,<代码S3.>

[code]use tempdb
go
Declare @1 Table
(
ID int primary key clustered,
Nr nvarchar(50),
OperationTime datetime default (getdate())
)

Alter Table @1 Add Constraint [CK_@1_Nr] Check(Nr Between '10001' And '19999')

[img=798 border=0,328 alt=image src=]https://www.caogenba.net/[/img]  
在<代码S2.>和<代码S3.>中可以发现,在剖析T-SQL语法过程就发生错误,也就是SQL Server不支持定义表变量时对Constraint命名,也不支持定义表变量后,对其建Constraint。

这里慎重提示下,在<代码S1.>给暂时表建Constraint的时间,特殊是在并发场景中,不要指定详细的Constraint名称,否则会发生对象已存在的错误提示。
e.g. 在MSSMS中我们先实行之前<代码S1.>的创建暂时表#1,不关闭当前会话的情况下,另建一个查询,实行与<代码S1.>雷同的代码,如图
[img=798 border=0,285 alt=image src=]https://www.caogenba.net/[/img]

左边的查询窗口,是实行原先的<代码S1.>,右边的查询窗口,是后实行雷同的<代码S1.>。在这里,我们注意赤色圈圈部分,发如今创建暂时表#1的过程,明确给了一个主键名称“PK_#1_ID”,当右边再创建雷同暂时表#1的时间就发生了对象重复错误问题。我们也可以通过SQL Server提供的体系视图sys.objects查询束缚“PK_#1_ID”的信息,
[code]use tempdb

go

Select * from sys.objects Where name='PK_#1_ID'
[img=798 border=0,269 alt=image src=]https://www.caogenba.net/[/img]
在体系视图sys.objects,发现“PK_#1_ID”名称反面不加怎样的随机数值表述不同会话有不同的对象。根据SQL Server对sys.objects的描述规则,sys.objects中的Name列数据是唯一的。当另一个会话创建雷同的对象时就会发生对象重复的错误。

在Constraint中,Foreign Key是不能应用与表变量,对于暂时表,创建Foreign Key是没故意义的。也就是说暂时表不受Foreign Key束缚。下面我们通过例子来说明暂时表的情况,
e.g.< 脚本S4.>
[code]use tempdb
go
if object_id('Tempdb..#1') Is Not Null
Drop Table #1
Go
if object_id('Tempdb..#2') Is Not Null
Drop Table #2
Go
Create Table #1
(

ID int,
Nr nvarchar(50) not null,
OperationTime datetime default(getdate()),
Constraint PK_#1_ID Primary Key(ID)
)
Alter Table #1 Add Constraint CK_#1_Nr Check(Nr Between '10001' And '19999')
Create table #2
(
ID int Primary Key,
ForeignID int Not null ,foreign Key(ForeignID) References #1(ID)
)
Go
[img=798 border=0,375 alt=image src=]https://www.caogenba.net/[/img]

可以看出对于暂时表不强制Foreign Key束缚,我们也可以通过SQL Server体系视图sys.foreign_keys查询
[code]use tempdb
go
Select * from sys.tables Where name like '#[1-2]%'
Select * From sys.foreign_keys

[img=798 border=0,361 alt=image src=]https://www.caogenba.net/[/img]右边的查询,只看到在sys.tables表哦中存在刚才创建的暂时表#1和#2,在sys.foreign_keys看不到有关Foreign Key束缚信息。这也验证了左边SQL Server提示的,在暂时表中无法强制使用Foreign Key束缚。

索引(Index)
从索引方面看暂时表和表变量,与从Constraint上分析有些类似,在暂时表中,它与真实表一样可以创建索引。在表变量定义过程中,也可以创建一些类似唯一和聚集索引。
e.g.< 脚本S5.>
[code]use tempdb

go

declare @1 Table(

ID int primary key clustered,

Nr nvarchar(50) unique Nonclustered

)

Insert into @1 (id,Nr) values(1,'10001')

Insert into @1 (id,Nr) values(2,'10002')

Insert into @1 (id,Nr) values(8,'10003')

Insert into @1 (id,Nr) values(3,'10004')

Insert into @1 (id,Nr) values(7,'10005')

Select top 2 *

From sys.indexes As a

Inner Join sys.tables As b On b.object_id=a.object_id

Order by b.create_date Desc

Select Nr From @1 Where Nr='10005'

go

[img=798 border=0,530 alt=image src=]https://www.caogenba.net/[/img]
[img=798 border=0,351 alt=image src=]https://www.caogenba.net/[/img]
上面截的是两张图,第一张图描述在表变量使聚集Primary Key,创建非聚集的Unique束缚,第二张图描述查询语句”Select Nr From @1 Where Nr='10005'” 应用到在表变量创建的唯一索引“UQ_#……”
是于暂时表索引的例子,我们拿一个例子说明,与前边说的Constraint例子有点相似,这里我们对暂时表创建索引,并给索引一个详细名称,测试是否会重复。
e.g.在MSSMS新增两个查询,编写下面的SQL语句:
< 脚本S6.>
[code]Use tempdb
Go
if object_id('#1') is not null
Drop Table #1

Create Table #1
(
ID int primary key,
Nr nvarchar(50) not null,
OperationTime datetime default (getdate()),
)

create nonclustered index IX_#1_Nr on #1(Nr Asc)
go
Select b.name As TableName,
a.*
from sys.indexes As a
Inner join sys.tables As b On b.object_id=a.object_id
Where b.name like '#1[_]%'
Order by b.create_date Asc
[img=798 border=0,394 alt=image src=]https://www.caogenba.net/[/img]

从返回的效果,我们看到在体系视图表Sys.Indexes中,创建有两个雷同的索引”IX_#1_Nr”,但注意下object_id数据不同。在SQL Server中是答应不同的表索引名称可以雷同的。在并发的情况下,按原理是可以对暂时表创建的索引给明确名称的。除非并发的情况会发生重复的表名或重复的Constraint,或别的体系资源不足的问题,才会导致出错。

I/0开销
暂时表与表变量,在I/O开销的描述,我们直接通过一个特殊的例子去描述它们,在MSSMS上新增两个查询,分别输入暂时表和表变量的测试代码:
e.g.< 脚本S7.>暂时表:
[code]Use tempdb
Go
if object_id('#1') is not null
Drop Table #1

Create Table #1
(
ID int primary key,
Nr nvarchar(50) not null,
OperationTime datetime default (getdate())
)

Insert into #1(ID,Nr,OperationTime)
Select top 50000 row_number()over (order by a.object_id),left(a.name+b.name,50) ,a.create_date
from master.sys.all_objects As a ,sys.all_columns As b
Where type='S'



Select Nr,count(Nr) As Sum_
From #1
Where Nr like 'sysrscolss%'
Group by Nr
< 脚本S8.>表变量:
[code]Use tempdb
Go
Declare @1 Table
(
ID int primary key,
Nr nvarchar(50) not null,
OperationTime datetime default (getdate())
)

Insert into @1(ID,Nr,OperationTime)
Select top 50000 row_number()over (order by a.object_id),left(a.name+b.name,50) ,a.create_date
from master.sys.all_objects As a ,sys.all_columns As b
Where type='S'


Select Nr,count(Nr) As Sum_
From @1
Where Nr like 'sysrscolss%'
Group by Nr

[img=798 border=0,513 alt=image src=]https://www.caogenba.net/[/img]

< 脚本S7.>和< 脚本S8.>,重要是看末了的查询语句I/O的开销,两者有何不同。通过上面的运行效果图形描述,可以看出查询开始,不管是暂时表照旧表变量,都使用到了聚集索引扫描(Clustered Index Scan),两者固然返回的数据一致,但I/O的开销不同。暂时表的I/O开销是0.324606,而表变量只有0.003125 相差非常大。在暂时表的实行计划图形中,我们发现一行“缺少索引(影响 71.9586):CREATE ……)”提示信息。我们对暂时表#1,在字段“Nr”上创建一个非聚集索引,再看实行实行效果:
[code]create nonclustered index IX_#1_Nr On #1(Nr)[img=798 border=0,527 alt=image src=]https://www.caogenba.net/[/img]
我们在暂时表#1上创建完索引“IX_#1_Nr”,运行看上面的图形显示,就感觉非常的故意思了。在暂时表#1查询时用了索引搜索(Index Seek),而且I/O开销减少到了0.0053742。固然开始查询的时间I/O开销照旧比表变量开始查询的时间大一些,但实行步调中比变变量少了一个“排序(Sort)”开销,后末了的看回Select效果,估计子树的成本比使用表变量的大大减少。
这里的例子只是描述一个特殊的情况,在真实的情况中,要根据现实的数据量来判断是否使用暂时表或表变量。倘若在存储过程中,当数据量非常少如只有不到50行记录,数据占的页面也不会超过1个页面,那么使用表变量是一个很好的解决方案。

作用域(scope)
表变量像局部变量(local variable)一样,有着很窄的作用域,只能应用于定义的函数、存储过程或批处理内。如,一个会话里面有几个批处理,那么表变量只能作用在它定义地点的批处理范围内。其他的批处理无法再调用它。
e.g.在MSSMS新增一个查询,编写< 脚本S9.>
[code]use tempdb
Go
Set Nocount on
declare @1 Table(
ID int primary key clustered,
Nr nvarchar(50) unique Nonclustered
)
Insert into @1 (id,Nr) values(1,'10001')
Insert into @1 (id,Nr) values(2,'10002')
Insert into @1 (id,Nr) values(8,'10003')
Insert into @1 (id,Nr) values(3,'10004')
Insert into @1 (id,Nr) values(7,'10005')

Select * From @1

Go --批处理结束点

Select * From @1
[img=798 border=0,384 alt=image src=]https://www.caogenba.net/[/img]
< 脚本S9.>地点的查询相当于一个会话,”Go”描述的一个批处理的结束点。在”Go”之前定义的表变量,在”Go”之后调用是发生“必须声明变量@1”的错误提示。
暂时表与表变量不同,暂时表的作用域是当前会话都有效,一直到会话结束或者暂时表被Drop的时间。也就是说可以跨当前会话的几个批处理范围。
e.g.< 脚本S10.>
[code]Use tempdb
go
if object_id('Tempdb..#1') Is Not Null
Drop Table #1
Go
Create Table #1
(
ID int,
Nr nvarchar(50) not null,
OperationTime datetime default (getdate()),
Constraint PK_#1_ID Primary Key (ID)
)
Select * from #1

go --批处理结束点

Select * from #1
[img=798 border=0,414 alt=image src=]https://www.caogenba.net/[/img]
< 脚本S10.>中可以看出在”GO”前后都可以查询到暂时表#1。
在描述暂时表与表变量的作用域时,有个地方要注意的是,当 sp_executesql 或 Execute 语句实行字符串时,字符串将作为它的自包含批处理实行. 假如表变量在sp_executesql 或 Execute 语句之前定义,在sp_executesql 或 Execute 语句的字符串中无法调用外部定义的表变量。
e.g.< 脚本S11.>
[code]use tempdb
go
Set nocount on
declare @1 Table(
ID int primary key clustered,
Nr nvarchar(50) unique Nonclustered
)
Insert into @1 (id,Nr) values(1,'10001')
Insert into @1 (id,Nr) values(2,'10002')
Insert into @1 (id,Nr) values(8,'10003')
Insert into @1 (id,Nr) values(3,'10004')
Insert into @1 (id,Nr) values(7,'10005')

Select * From @1

Execute(N'Select * From @1')

go
[img=798 border=0,378 alt=image src=]https://www.caogenba.net/[/img]
< 脚本S11.>中,当实行到”Execute(N'Select * From @1')”时间,同样发生与< 脚本S9.>一样的错误提示“必须声明变量@1”.
暂时表是可以在sp_executesql 或 Execute 语句实行字符串中被调用。这里不再举例子,假如你有所模糊可以参考< 脚本S11.>把表变量转成暂时表测试下就能加深明确与记忆。


存儲位置
说到暂时表和表变量的存储位置,我们可以看到有很多版本的说法,特殊是表变量。有的说表变量数据存储在内存中,有的说存储在数据库tempdb中,有的说有部分存储在内存,部分存储在数据库tempdb中。根据我查到的官方资料,说的是在SQL Server 2000下:
A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).
在SQL Server 2005\SQL2008的版本,表变量存储与暂时表有相似,都会在数据库tempdb创建,使用到tempdb存储空间。
e.g.< 脚本S12.>暂时表
[code]use tempdb
go
Set nocount on

Exec sp_spaceused /*插入数据之前*/

if object_id('#1') Is not null
Drop Table #1

create table #1(ID int ,Nr nvarchar(50))
Insert into #1 (ID,Nr)
Select top(1) row_number() Over(order By a.object_id),left(a.name+b.name,50)
From sys.all_objects As a,
sys.all_columns As b

Select top(1) name,object_id,type,create_date from sys.tables Order by create_date Desc

Exec sp_spaceused /*插入数据之后*/
Go


[img=798 border=0,619 alt=image src=]https://www.caogenba.net/[/img]在< 脚本S12.>实行后,我们可以看到在数据库tempdb中的表sys.tables创建有表#1。我们接着看空间的使用情况,插入数据之前,数据库未使用空间(unallocated space)为510.39MB,向暂时表#1插入1条数据后,数据库未使用空间为501.38MB,未使用空间变小了。再来看整个数据库的数据(data)使用的空间变革,从552KB变成560KB,使用了一页的数据空间(8kb)。这说明一点,暂时表,即使你只插入一条数据都会使用到数据库tempdb的空间。大概会有人问,要是我只建暂时表#1,不插入数据,会怎样。我们可以效果:
[img=798 border=0,686 alt=image src=]https://www.caogenba.net/[/img]
这里你会发现前后的空间大小不变,不外,不要认为没有使用到数据库tempdb数据空间,当你多用户创建暂时表结构的时间,你就会发现着实都会应用到数据库tempdb的空间。我这里创建了10个#1后的效果如:
[img=798 border=0,292 alt=image src=]https://www.caogenba.net/[/img]

雷同的原理,我们使用类似的方法测试表变量的情况,发现结论是与暂时表的一致的,会使用到数据库tempdb的空间.
e.g.< 脚本S13.>表变量
[code]use tempdb
go
Set nocount on
Exec sp_spaceused /*插入数据之前*/

Declare @1 table(ID int ,Nr nvarchar(50))
Insert into @1 (ID,Nr)
Select top(1) row_number() Over(order By a.object_id),left(a.name+b.name,50)
From sys.all_objects As a,
sys.all_columns As b

Select top(1) name,object_id,type,create_date from sys.objects Where type='U' Order by create_date Desc

Exec sp_spaceused /*插入数据之后*/

Go
Exec sp_spaceused /*Go之后*/
[img=798 border=0,643 alt=image src=]https://www.caogenba.net/[/img]
< 脚本S13.>中,我多写了一个”GO”之后检查空间大小的存储过程sp_spaceused。如许为了了更能体现表变量使用空间变革情况。从插入数据前和插入数据后的效果图来看,表变量不仅在数据库tempdb创建了表结构#267ABA7A类似的如许表,表变量也应用到了数据库tempdb的空间。不外这里注意一点就是在”Go”之后,我们发现表变量@1,会立刻释放所使用的数据空间。为了更能体现使用空间情况。我们可以向表变量@1插入大量数据看空间变革情况(测试插入1000万的数据行)。
e.g.< 脚本S14.>
[code]use tempdb
go
Set nocount on
Exec sp_spaceused /*插入数据之前*/

Declare @1 table(ID int ,Nr nvarchar(50))
Insert into @1 (ID,Nr)
Select top(10000000) row_number() Over(order By a.object_id),left(a.name+b.name,50)
From sys.all_objects As a,
sys.all_columns As b

Select top(1) name,object_id,type,create_date from sys.objects Where type='U' Order by create_date Desc

Exec sp_spaceused /*插入数据之后*/

Go
Exec sp_spaceused /*Go之后*/
[img=798 border=0,649 alt=image src=]https://www.caogenba.net/[/img]
这里我们可清楚的看到数据库tempdb的大小(database_size)变革情况,从插入数据前的552.75MB变成插入数据之后的892.75MB。非常故意思的是我们在”Go之后”发现数据库大小生存在892.75MB,但数据使用空间(data)从560KB—>851464KB—>536KB ,说明SQL Server自动释放为使用的数据空间,但不会立刻自动释放数据库分配的磁盘空间。我们在现实的情况中,发现暂时数据库tempdb使用的磁盘空间越来越大,这是其中的原因之一。


其他
暂时表与表变量,还有其他的特征,如暂时表受事务回滚,而表变量不受事务回滚影响。对应事务方面,更为精确的说法是表变量的事务只在表变量更新期间存在。因此减少了表变量对锁定和记录资源的需求。
e.g.< 脚本S15.>

[code]use tempdb
go
Set nocount on

if object_id('#1') Is not null
Drop Table #1
create table #1(ID int ,Nr nvarchar(50))
Declare @1 table(ID int ,Nr nvarchar(50))

begin tran /*开始事务*/

Insert into #1 (ID,Nr)
Select top(1) row_number() Over(order By a.object_id),left(a.name+b.name,50)
From sys.all_objects As a,
sys.all_columns As b


Insert into @1 (ID,Nr)
Select top(1) row_number() Over(order By a.object_id),left(a.name+b.name,50)
From sys.all_objects As a,
sys.all_columns As b

rollback tran /*回滚事务*/

Select * from #1
Select * from @1

Go

[img=798 border=0,560 alt=image src=]https://www.caogenba.net/[/img]
这里发现”Rollback Tran”之后,暂时表#1没有数据插入,而表变量@1还有一条数据存在。说明表变量不受”Rollback Tran”所影响。它的举动有类似于局部变量一样。
别的SQL Server对表变量不生存任何的统计信息,由于云云,我们在数据量大的时间使用表变量,发现比暂时表要慢很多。前面在I/O开销那边我们取有一个特殊的例子,这里不再举例。

小结
无论怎样,暂时表和表变量有各自的特征,有自己长处和缺点。在不同的场景选择它们灵活应用。本文章是我对暂时表和表变量的一些熟悉明确,大概有些地方说的不够好或者遗漏,你可以留言或Email与我联系,我会继承改进或改正,我也不盼望有些错误的见解会误导别人。正如Phil Factor说的一句" I'd hate to think of anyone being misled by my advice!".

附参考:
http://support.microsoft.com/kb/305977/en-us
http://stackoverflow.com/questions/27894/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server
http://msdn.microsoft.com/en-us/library/aa175774(SQL.80).aspx
http://msdn.microsoft.com/en-us/library/cc966545.aspx
http://www.simple-talk.com/sql/t-sql-programming/temporary-tables-in-sql-server/
http://support.microsoft.com/kb/942661/en-us

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作