例子描述
约束(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)
)
临时表与表变量不同,临时表的作用域是当前会话都有效,一直到会话结束或者临时表被Drop的时候。也就是说可以跨当前会话的几个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