sqlserver 臨時表 Vs 表變量 詳細介紹

例子描述

約束(Constraint)

           在臨時表和表變量，都可以創(chuàng)建Constraint。針對表變量，只有定義時能加Constraint。

e.g.在Microsoft SQL Server Management Studio（MSSMS）查詢中，創(chuàng)建臨時表并建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的創(chuàng)建時，創(chuàng)建Constraint如“Constraint PK_#1_ID Primary Key(ID)”，也可以在創(chuàng)建臨時表#1后創(chuàng)建Constraint，如“Alter Table #1 Add Constraint CK_#1_Nr Check(Nr Between '10001' And'19999')”，下面我們來看表變量的場景，在定義表變量時不能指定Constraint名，定義表變量后不能對表變量創(chuàng)建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)
)

在定義表變量后不能對表變量創(chuàng)建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.>中可以發(fā)現(xiàn)，在解析T-SQL語法過程就發(fā)生錯誤，也就是SQL Server不支持定義表變量時對Constraint命名，也不支持定義表變量后，對其建Constraint。

這里慎重提示下，在代碼S1.>給臨時表建Constraint的時候，特別是在并發(fā)場景中，不要指定具體的Constraint名稱，不然會發(fā)生對象已存在的錯誤提示。

e.g. 在MSSMS中我們先執(zhí)行之前代碼S1.>的創(chuàng)建臨時表#1，不關(guān)閉當前會話的情況下，另建一個查詢，執(zhí)行與代碼S1.>相同的代碼，如圖

左邊的查詢窗口，是執(zhí)行原先的代碼S1.>，右邊的查詢窗口，是后執(zhí)行相同的代碼S1.>。在這里，我們注意紅色圈圈部分，發(fā)現(xiàn)在創(chuàng)建臨時表#1的過程，明確給了一個主鍵名稱“PK_#1_ID”，當右邊再創(chuàng)建相同臨時表#1的時候就發(fā)生了對象重復(fù)錯誤問題。我們也可以通過SQL Server提供的系統(tǒng)視圖sys.objects查詢約束“PK_#1_ID”的信息，

use tempdb

go

Select * from sys.objects Where name='PK_#1_ID'

在系統(tǒng)視圖sys.objects，發(fā)現(xiàn)“PK_#1_ID”名稱后面不加如何的隨機數(shù)值表述不同會話有不同的對象。根據(jù)SQL Server對sys.objects的描述規(guī)則，sys.objects中的Name列數(shù)據(jù)是唯一的。當另一個會話創(chuàng)建相同的對象時就會發(fā)生對象重復(fù)的錯誤。

在Constraint中，F(xiàn)oreign Key是不能應(yīng)用與表變量，對于臨時表，創(chuàng)建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系統(tǒng)視圖sys.foreign_keys查詢

use tempdb
go
Select * from sys.tables Where name like '#[1-2]%'
Select * From sys.foreign_keys

右邊的查詢，只看到在sys.tables表哦中存在剛才創(chuàng)建的臨時表#1和#2，在sys.foreign_keys看不到有關(guān)Foreign Key約束信息。這也驗證了左邊SQL Server提示的，在臨時表中無法強制使用Foreign Key約束。

索引(Index)

從索引方面看臨時表和表變量，與從Constraint上分析有些類似，在臨時表中，它與真實表一樣可以創(chuàng)建索引。在表變量定義過程中，也可以創(chuàng)建一些類似唯一和聚集索引。

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，創(chuàng)建非聚集的Unique約束，第二張圖描述查詢語句”Select Nr From @1 Where Nr='10005'” 應(yīng)用到在表變量創(chuàng)建的唯一索引“UQ_#……”

是于臨時表索引的例子，我們拿一個例子說明，與前邊說的Constraint例子有點相似，這里我們對臨時表創(chuàng)建索引，并給索引一個具體名稱，測試是否會重復(fù)。

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

從返回的結(jié)果，我們看到在系統(tǒng)視圖表Sys.Indexes中，創(chuàng)建有兩個相同的索引”IX_#1_Nr”，但注意下object_id數(shù)據(jù)不同。在SQL Server中是允許不同的表索引名稱可以相同的。在并發(fā)的環(huán)境下，按原理是可以對臨時表創(chuàng)建的索引給明確名稱的。除非并發(fā)的情況會發(fā)生重復(fù)的表名或重復(fù)的Constraint，或其它系統(tǒng)資源不足的問題，才會導(dǎo)致出錯。

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的開銷，兩者有何不同。通過上面的運行結(jié)果圖形描述，可以看出查詢開始，不管是臨時表還是表變量，都使用到了聚集索引掃描(Clustered Index Scan)，兩者雖然返回的數(shù)據(jù)一致，但I/O的開銷不同。臨時表的I/O開銷是0.324606,而表變量只有0.003125 相差非常大。在臨時表的執(zhí)行計劃圖形中，我們發(fā)現(xiàn)一行“缺少索引（影響 71.9586):CREATE ……）”提示信息。我們對臨時表#1，在字段“Nr”上創(chuàng)建一個非聚集索引，再看執(zhí)行執(zhí)行結(jié)果：

create nonclustered index IX_#1_Nr On #1(Nr)

我們在臨時表#1上創(chuàng)建完索引“IX_#1_Nr”，運行看上面的圖形顯示，就感覺非常的有意思了。在臨時表#1查詢時用了索引搜索（Index Seek），而且I/O開銷減少到了0.0053742。雖然開始查詢的時候I/O開銷還是比表變量開始查詢的時候大一些，但執(zhí)行步驟中比變變量少了一個“排序(Sort)”開銷，后最后的看回Select結(jié)果，估計子樹的成本比使用表變量的大大減少。

這里的例子只是描述一個特殊的情況，在真實的環(huán)境中，要根據(jù)實際的數(shù)據(jù)量來判斷是否使用臨時表或表變量。倘若在存儲過程中，當數(shù)據(jù)量非常少如只有不到50行記錄，數(shù)據(jù)占的頁面也不會超過1個頁面，那么使用表變量是一個很好的解決方案。

作用域(scope)

表變量像局部變量(local variable)一樣，有著很窄的作用域，只能應(yīng)用于定義的函數(shù)、存儲過程或批處理內(nèi)。如，一個會話里面有幾個批處理，那么表變量只能作用在它定義所在的批處理范圍內(nèi)。其他的批處理無法再調(diào)用它。

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 --批處理結(jié)束點

Select * From @1

腳本S9.>所在的查詢相當于一個會話，”Go”描述的一個批處理的結(jié)束點。在”Go”之前定義的表變量，在”Go”之后調(diào)用是發(fā)生“必須聲明變量@1”的錯誤提示。

臨時表與表變量不同，臨時表的作用域是當前會話都有效，一直到會話結(jié)束或者臨時表被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 --批處理結(jié)束點

Select * from #1

腳本S10.>中可以看出在”GO”前后都可以查詢到臨時表#1。

在描述臨時表與表變量的作用域時，有個地方要注意的是，當 sp_executesql 或 Execute 語句執(zhí)行字符串時，字符串將作為它的自包含批處理執(zhí)行. 如果表變量在sp_executesql 或 Execute 語句之前定義，在sp_executesql 或 Execute 語句的字符串中無法調(diào)用外部定義的表變量。

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.>中，當執(zhí)行到”Execute(N'Select * From @1')”時候，同樣發(fā)生與 腳本S9.>一樣的錯誤提示“必須聲明變量@1”.

臨時表是可以在sp_executesql 或 Execute 語句執(zhí)行字符串中被調(diào)用。這里不再舉例子，如果你有所模糊可以參考 腳本S11.>把表變量轉(zhuǎn)成臨時表測試下就能加深理解與記憶。

存儲位置

說到臨時表和表變量的存儲位置，我們可以看到有很多版本的說法，特別是表變量。有的說表變量數(shù)據(jù)存儲在內(nèi)存中，有的說存儲在數(shù)據(jù)庫tempdb中，有的說有部分存儲在內(nèi)存，部分存儲在數(shù)據(jù)庫tempdb中。根據(jù)我查到的官方資料，說的是在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的版本，表變量存儲與臨時表有相似，都會在數(shù)據(jù)庫tempdb創(chuàng)建，使用到tempdb存儲空間。

e.g. 腳本S12.>臨時表

use tempdb
go
Set nocount on

Exec sp_spaceused /*插入數(shù)據(jù)之前*/

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 /*插入數(shù)據(jù)之后*/
Go

在 腳本S12.>執(zhí)行后，我們可以看到在數(shù)據(jù)庫tempdb中的表sys.tables創(chuàng)建有表#1。我們接著看空間的使用情況，插入數(shù)據(jù)之前，數(shù)據(jù)庫未使用空間(unallocated space)為510.39MB,向臨時表#1插入1條數(shù)據(jù)后，數(shù)據(jù)庫未使用空間為501.38MB,未使用空間變小了。再來看整個數(shù)據(jù)庫的數(shù)據(jù)(data)使用的空間變化,從552KB變成560KB,使用了一頁的數(shù)據(jù)空間(8kb)。這說明一點，臨時表，即使你只插入一條數(shù)據(jù)都會使用到數(shù)據(jù)庫tempdb的空間。也許會有人問，要是我只建臨時表#1,不插入數(shù)據(jù)，會如何。我們可以結(jié)果：

這里你會發(fā)現(xiàn)前后的空間大小不變，不過，不要認為沒有使用到數(shù)據(jù)庫tempdb數(shù)據(jù)空間，當你多用戶創(chuàng)建臨時表結(jié)構(gòu)的時候，你就會發(fā)現(xiàn)其實都會應(yīng)用到數(shù)據(jù)庫tempdb的空間。我這里創(chuàng)建了10個#1后的效果如：

相同的原理，我們使用類似的方法測試表變量的情況，發(fā)現(xiàn)結(jié)論是與臨時表的一致的，會使用到數(shù)據(jù)庫tempdb的空間.

e.g. 腳本S13.>表變量

use tempdb
go
Set nocount on
Exec sp_spaceused /*插入數(shù)據(jù)之前*/

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 /*插入數(shù)據(jù)之后*/

Go
Exec sp_spaceused /*Go之后*/

腳本S13.>中，我多寫了一個”GO”之后檢查空間大小的存儲過程sp_spaceused。這樣為了了更能體現(xiàn)表變量使用空間變化情況。從插入數(shù)據(jù)前和插入數(shù)據(jù)后的結(jié)果圖來看，表變量不僅在數(shù)據(jù)庫tempdb創(chuàng)建了表結(jié)構(gòu)#267ABA7A類似的這樣表，表變量也應(yīng)用到了數(shù)據(jù)庫tempdb的空間。不過這里注意一點就是在”Go”之后，我們發(fā)現(xiàn)表變量@1,會馬上釋放所使用的數(shù)據(jù)空間。為了更能體現(xiàn)使用空間情況。我們可以向表變量@1插入大量數(shù)據(jù)看空間變化情況（測試插入1000萬的數(shù)據(jù)行）。

e.g. 腳本S14.>

use tempdb
go
Set nocount on
Exec sp_spaceused /*插入數(shù)據(jù)之前*/

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 /*插入數(shù)據(jù)之后*/

Go
Exec sp_spaceused /*Go之后*/

這里我們可清晰的看到數(shù)據(jù)庫tempdb的大小(database_size)變化情況，從插入數(shù)據(jù)前的552.75MB變成插入數(shù)據(jù)之后的892.75MB。非常有意思的是我們在”Go之后”發(fā)現(xiàn)數(shù)據(jù)庫大小保存在892.75MB,但數(shù)據(jù)使用空間(data)從560KB—>851464KB—>536KB ，說明SQL Server自動釋放為使用的數(shù)據(jù)空間，但不會馬上自動釋放數(shù)據(jù)庫分配的磁盤空間。我們在實際的環(huán)境中，發(fā)現(xiàn)臨時數(shù)據(jù)庫tempdb使用的磁盤空間越來越大，這是其中的原因之一。

其他

臨時表與表變量，還有其他的特征，如臨時表受事務(wù)回滾，而表變量不受事務(wù)回滾影響。對應(yīng)事務(wù)方面，更為正確的說法是表變量的事務(wù)只在表變量更新期間存在。因此減少了表變量對鎖定和記錄資源的需求。

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 /*開始事務(wù)*/

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 /*回滾事務(wù)*/

Select * from #1
Select * from @1

Go

這里發(fā)現(xiàn)”Rollback Tran”之后，臨時表#1沒有數(shù)據(jù)插入，而表變量@1還有一條數(shù)據(jù)存在。說明表變量不受”Rollback Tran”所影響。它的行為有類似于局部變量一樣。

另外SQL Server對表變量不保留任何的統(tǒng)計信息，因為如此，我們在數(shù)據(jù)量大的時候使用表變量，發(fā)現(xiàn)比臨時表要慢許多。前面在I/O開銷那里我們?nèi)∮幸粋€特殊的例子，這里不再舉例。

小結(jié)

無論如何，臨時表和表變量有各自的特征，有自己優(yōu)點和缺點。在不同的場景選擇它們靈活應(yīng)用。本文章是我對臨時表和表變量的一些認識理解，可能有些地方說的不夠好或者遺漏，你可以留言或Email與我聯(lián)系，我會繼續(xù)改進或糾正，我也不希望有些錯誤的見解會誤導(dǎo)別人。正如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