程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> MSSQL >> sqlserver 暫時表 Vs 表變量具體引見

sqlserver 暫時表 Vs 表變量具體引見

編輯：MSSQL

sqlserver 暫時表 Vs 表變量具體引見。本站提示廣大學習愛好者：（sqlserver 暫時表 Vs 表變量具體引見）文章只能為提供參考，不一定能成為您想要的結果。以下是sqlserver 暫時表 Vs 表變量具體引見正文

這裡我們在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) values(3,'10004')
Insert into @1 (id,Nr) values(7,'10005')

Select * From @1

Execute(N'Select * From @1')

go

< 劇本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.>暫時表

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

在< 劇本S12.>履行後，我們可以看到在數據庫tempdb中的表sys.tables創立有表#1。我們接著看空間的應用情形，拔出數據之前，數據庫未應用空間(unallocated space)為510.39MB,向暫時表#1拔出1條數據後，數據庫未應用空間為501.38MB,未應用空間變小了。再來看全部數據庫的數據(data)應用的空間變更,從552KB釀成560KB,應用了一頁的數據空間(8kb)。這解釋一點，暫時表，即便你只拔出一條數據都邑應用到數據庫tempdb的空間。或許會有人問，如果我只建暫時表#1,不拔出數據，會若何。我們可以成果：

這裡你會發明前後的空間年夜小不變，不外，不要以為沒有應用到數據庫tempdb數據空間，當你多用戶創立暫時表構造的時刻，你就會發明其實都邑運用到數據庫tempdb的空間。我這裡創立了10個#1後的後果如：

雷同的道理，我們應用相似的辦法測試表變量的情形，發明結論是與暫時表的分歧的，會應用到數據庫tempdb的空間.

e.g.< 劇本S13.>表變量

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以後*/

< 劇本S13.>中，我多寫了一個”GO”以後檢討空間年夜小的存儲進程sp_spaceused。如許為了了更能表現表變量應用空間變更情形。從拔出數據前和拔出數據後的成果圖來看，表變量不只在數據庫tempdb創立了表構造#267ABA7A相似的如許表，表變量也運用到了數據庫tempdb的空間。不外這裡留意一點就是在”Go”以後，我們發明表變量@1,會立時釋放所應用的數據空間。為了更能表現應用空間情形。我們可以向表變量@1拔出年夜量數據看空間變更情形（測試拔出1000萬的數據行）。

e.g.< 劇本S14.>

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以後*/

這裡我們可清楚的看到數據庫tempdb的年夜小(database_size)變更情形，從拔出數據前的552.75MB釀成拔出數據以後的892.75MB。異常成心思的是我們在”Go以後”發明數據庫年夜小保留在892.75MB,但數據應用空間(data)從560KB—>851464KB—>536KB ，解釋SQL Server主動釋放為應用的數據空間，但不會立時主動釋放數據庫分派的磁盤空間。我們在現實的情況中，發明暫時數據庫tempdb應用的磁盤空間愈來愈年夜，這是個中的緣由之一。

其他

暫時表與表變量，還有其他的特點，如暫時表受事務回滾，而表變量不受事務回滾影響。對應事務方面，更加准確的說法是表變量的事務只在表變量更新時代存在。是以削減了表變量對鎖定和記載資本的需求。

e.g.< 劇本S15.>

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

這裡發明”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

MSSQL