SQL Server临时表 Vs 表变量用法示例

感兴趣的小伙伴，下面一起跟随脚本之家 jb51.cc的小编两巴掌来看看吧！

例子描述

约束(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,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,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语句：

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!