这里我们在SQL Server 2005\SQL Server 2008版本上通过举例子,说明临时表和表变量两者的一些特征,让我们对临时表和表变量有进一步的认识。在本章中,我们将从下面几个方面去进行描述,对其中的一些特征举例子说明:
约束(Constraint) 索引(Index) I/0开销 作用域(scope) 存儲位置 其他
例子描述
约束(Constraint)
在临时表和表变量,都可以创建Constraint。针对表变量,只有定义时能加Constraint。
e.g.在Microsoft SQL Server Management Studio(MSSMS)查询中,创建临时表并建Constraint场景,<脚本S1.>
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.>
Use tempdb
Go
Declare @1 Table
(
ID int,
Nr nvarchar(50) not null,
OperationTime datetime default (getdate()),
Constraint [PK_@1_ID] Primary Key (ID)
)
在定义表变量后不能对表变量创建Constraint,<代码S3.>
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')
在<代码S2.>和<代码S3.>中可以发现,在解析T-SQL语法过程就发生错误,也就是SQL Server不支持定义表变量时对Constraint命名,也不支持定义表变量后,对其建Constraint。
这里慎重提示下,在<代码S1.>给临时表建Constraint的时候,特别是在并发场景中,不要指定具体的Constraint名称,不然会发生对象已存在的错误提示。
e.g. 在MSSMS中我们先执行之前<代码S1.>的创建临时表#1,不关闭当前会话的情况下,另建一个查询,执行与<代码S1.>相同的代码,如图
左边的查询窗口,是执行原先的<代码S1.>,右边的查询窗口,是后执行相同的<代码S1.>。在这里,我们注意红色圈圈部分,发现在创建临时表#1的过程,明确给了一个主键名称“PK_#1_ID”,当右边再创建相同临时表#1的时候就发生了对象重复错误问题。我们也可以通过SQL Server提供的系统视图sys.objects查询约束“PK_#1_ID”的信息,
use tempdb
go
Select * from sys.objects Where name='PK_#1_ID'
在系统视图sys.objects,发现“PK_#1_ID”名称后面不加如何的随机数值表述不同会话有不同的对象。根据SQL Server对sys.objects的描述规则,sys.objects中的Name列数据是唯一的。当另一个会话创建相同的对象时就会发生对象重复的错误。
在Constraint中,Foreign Key是不能应用与表变量,对于临时表,创建Foreign Key是没有意义的。也就是说临时表不受Foreign Key约束。下面我们通过例子来说明临时表的情况,
e.g.< 脚本S4.>
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
可以看出对于临时表不强制Foreign Key约束,我们也可以通过SQL Server系统视图sys.foreign_keys查询
use tempdb
go
Select * from sys.tables Where name like '#[1-2]%'
Select * From sys.foreign_keys
右边的查询,只看到在sys.tables表哦中存在刚才创建的临时表#1和#2,在sys.foreign_keys看不到有关Foreign Key约束信息。这也验证了左边SQL Server提示的,在临时表中无法强制使用Foreign Key约束。
索引(Index)
从索引方面看临时表和表变量,与从Constraint上分析有些类似,在临时表中,它与真实表一样可以创建索引。在表变量定义过程中,也可以创建一些类似唯一和聚集索引。
e.g.< 脚本S5.>
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
上面截的是两张图,第一张图描述在表变量使聚集Primary Key,创建非聚集的Unique约束,第二张图描述查询语句”Select Nr From @1 Where Nr='10005'” 应用到在表变量创建的唯一索引“UQ_#……”
是于临时表索引的例子,我们拿一个例子说明,与前边说的Constraint例子有点相似,这里我们对临时表创建索引,并给索引一个具体名称,测试是否会重复。
e.g.在MSSMS新增两个查询,编写下面的SQL语句:
< 脚本S6.>
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
从返回的结果,我们看到在系统视图表Sys.Indexes中,创建有两个相同的索引”IX_#1_Nr”,但注意下object_id数据不同。在SQL Server中是允许不同的表索引名称可以相同的。在并发的环境下,按原理是可以对临时表创建的索引给明确名称的。除非并发的情况会发生重复的表名或重复的Constraint,或其它系统资源不足的问题,才会导致出错。
I/0开销
临时表与表变量,在I/O开销的描述,我们直接通过一个特殊的例子去描述它们,在MSSMS上新增两个查询,分别输入临时表和表变量的测试代码:
e.g.< 脚本S7.>临时表:
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.>表变量:
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
< 脚本S7.>和< 脚本S8.>,主要是看最后的查询语句I/O的开销,两者有何不同。通过上面的运行结果图形描述,可以看出查询开始,不管是临时表还是表变量,都使用到了聚集索引扫描(Clustered Index Scan),两者虽然返回的数据一致,但I/O的开销不同。临时表的I/O开销是0.324606,而表变量只有0.003125 相差非常大。在临时表的执行计划图形中,我们发现一行“缺少索引(影响 71.9586):CREATE ……)”提示信息。我们对临时表#1,在字段“Nr”上创建一个非聚集索引,再看执行执行结果:
create nonclustered index IX_#1_Nr On #1(Nr)
我们在临时表#1上创建完索引“IX_#1_Nr”,运行看上面的图形显示,就感觉非常的有意思了。在临时表#1查询时用了索引搜索(Index Seek),而且I/O开销减少到了0.0053742。虽然开始查询的时候I/O开销还是比表变量开始查询的时候大一些,但执行步骤中比变变量少了一个“排序(Sort)”开销,后最后的看回Select结果,估计子树的成本比使用表变量的大大减少。
这里的例子只是描述一个特殊的情况,在真实的环境中,要根据实际的数据量来判断是否使用临时表或表变量。倘若在存储过程中,当数据量非常少如只有不到50行记录,数据占的页面也不会超过1个页面,那么使用表变量是一个很好的解决方案。
作用域(scope)
表变量像局部变量(local variable)一样,有着很窄的作用域,只能应用于定义的函数、存储过程或批处理内。如,一个会话里面有几个批处理,那么表变量只能作用在它定义所在的批处理范围内。其他的批处理无法再调用它。
e.g.在MSSMS新增一个查询,编写< 脚本S9.>
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
< 脚本S9.>所在的查询相当于一个会话,”Go”描述的一个批处理的结束点。在”Go”之前定义的表变量,在”Go”之后调用是发生“必须声明变量@1”的错误提示。
临时表与表变量不同,临时表的作用域是当前会话都有效,一直到会话结束或者临时表被Drop的时候。也就是说可以跨当前会话的几个批处理范围。
e.g.< 脚本S10.>
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
< 脚本S10.>中可以看出在”GO”前后都可以查询到临时表#1。
在描述临时表与表变量的作用域时,有个地方要注意的是,当 sp_executesql 或 Execute 语句执行字符串时,字符串将作为它的自包含批处理执行. 如果表变量在sp_executesql 或 Execute 语句之前定义,在sp_executesql 或 Execute 语句的字符串中无法调用外部定义的表变量。
e.g.< 脚本S11.>
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)