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