sqlserver 臨時(shí)表 Vs 表變量 詳細(xì)介紹
例子描述
約束(Constraint)
在臨時(shí)表和表變量，都可以創(chuàng)建Constraint。針對表變量，只有定義時(shí)能加Constraint。
e.g.在Microsoft SQL Server Management Studio（MSSMS）查詢中，創(chuàng)建臨時(shí)表并建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.>中，可以看出在臨時(shí)表#1的創(chuàng)建時(shí)，創(chuàng)建Constraint如“Constraint PK_#1_ID Primary Key(ID)”，也可以在創(chuàng)建臨時(shí)表#1后創(chuàng)建Constraint，如“Alter Table #1 Add Constraint CK_#1_Nr Check(Nr Between '10001' And'19999')”，下面我們來看表變量的場景，在定義表變量時(shí)不能指定Constraint名，定義表變量后不能對表變量創(chuàng)建Constraint。
e.g. 在定義表變量時(shí)不能指定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ā)生錯(cuò)誤，也就是SQL Server不支持定義表變量時(shí)對Constraint命名，也不支持定義表變量后，對其建Constraint。
這里慎重提示下，在<代碼S1.>給臨時(shí)表建Constraint的時(shí)候，特別是在并發(fā)場景中，不要指定具體的Constraint名稱，不然會發(fā)生對象已存在的錯(cuò)誤提示。
e.g. 在MSSMS中我們先執(zhí)行之前<代碼S1.>的創(chuàng)建臨時(shí)表#1，不關(guān)閉當(dāng)前會話的情況下，另建一個(gè)查詢，執(zhí)行與<代碼S1.>相同的代碼，如圖
左邊的查詢窗口，是執(zhí)行原先的<代碼S1.>，右邊的查詢窗口，是后執(zhí)行相同的<代碼S1.>。在這里，我們注意紅色圈圈部分，發(fā)現(xiàn)在創(chuàng)建臨時(shí)表#1的過程，明確給了一個(gè)主鍵名稱“PK_#1_ID”，當(dāng)右邊再創(chuàng)建相同臨時(shí)表#1的時(shí)候就發(fā)生了對象重復(fù)錯(cuò)誤問題。我們也可以通過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”名稱后面不加如何的隨機(jī)數(shù)值表述不同會話有不同的對象。根據(jù)SQL Server對sys.objects的描述規(guī)則，sys.objects中的Name列數(shù)據(jù)是唯一的。當(dāng)另一個(gè)會話創(chuàng)建相同的對象時(shí)就會發(fā)生對象重復(fù)的錯(cuò)誤。
在Constraint中，F(xiàn)oreign Key是不能應(yīng)用與表變量，對于臨時(shí)表，創(chuàng)建Foreign Key是沒有意義的。也就是說臨時(shí)表不受Foreign Key約束。下面我們通過例子來說明臨時(shí)表的情況，
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
可以看出對于臨時(shí)表不強(qiáng)制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)建的臨時(shí)表#1和#2，在sys.foreign_keys看不到有關(guān)Foreign Key約束信息。這也驗(yàn)證了左邊SQL Server提示的，在臨時(shí)表中無法強(qiáng)制使用Foreign Key約束。
索引(Index)
從索引方面看臨時(shí)表和表變量，與從Constraint上分析有些類似，在臨時(shí)表中，它與真實(shí)表一樣可以創(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_#……”
是于臨時(shí)表索引的例子，我們拿一個(gè)例子說明，與前邊說的Constraint例子有點(diǎn)相似，這里我們對臨時(shí)表創(chuàng)建索引，并給索引一個(gè)具體名稱，測試是否會重復(fù)。
e.g.在MSSMS新增兩個(gè)查詢，編寫下面的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)建有兩個(gè)相同的索引”IX_#1_Nr”，但注意下object_id數(shù)據(jù)不同。在SQL Server中是允許不同的表索引名稱可以相同的。在并發(fā)的環(huán)境下，按原理是可以對臨時(shí)表創(chuàng)建的索引給明確名稱的。除非并發(fā)的情況會發(fā)生重復(fù)的表名或重復(fù)的Constraint，或其它系統(tǒng)資源不足的問題，才會導(dǎo)致出錯(cuò)。
I/0開銷
臨時(shí)表與表變量，在I/O開銷的描述，我們直接通過一個(gè)特殊的例子去描述它們，在MSSMS上新增兩個(gè)查詢，分別輸入臨時(shí)表和表變量的測試代碼：
e.g.< 腳本S7.>臨時(shí)表：
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的開銷，兩者有何不同。通過上面的運(yùn)行結(jié)果圖形描述，可以看出查詢開始，不管是臨時(shí)表還是表變量，都使用到了聚集索引掃描(Clustered Index Scan)，兩者雖然返回的數(shù)據(jù)一致，但I(xiàn)/O的開銷不同。臨時(shí)表的I/O開銷是0.324606,而表變量只有0.003125 相差非常大。在臨時(shí)表的執(zhí)行計(jì)劃圖形中，我們發(fā)現(xiàn)一行“缺少索引（影響 71.9586):CREATE ……）”提示信息。我們對臨時(shí)表#1，在字段“Nr”上創(chuàng)建一個(gè)非聚集索引，再看執(zhí)行執(zhí)行結(jié)果：
create nonclustered index IX_#1_Nr On #1(Nr)
我們在臨時(shí)表#1上創(chuàng)建完索引“IX_#1_Nr”，運(yùn)行看上面的圖形顯示，就感覺非常的有意思了。在臨時(shí)表#1查詢時(shí)用了索引搜索（Index Seek），而且I/O開銷減少到了0.0053742。雖然開始查詢的時(shí)候I/O開銷還是比表變量開始查詢的時(shí)候大一些，但執(zhí)行步驟中比變變量少了一個(gè)“排序(Sort)”開銷，后最后的看回Select結(jié)果，估計(jì)子樹的成本比使用表變量的大大減少。
這里的例子只是描述一個(gè)特殊的情況，在真實(shí)的環(huán)境中，要根據(jù)實(shí)際的數(shù)據(jù)量來判斷是否使用臨時(shí)表或表變量。倘若在存儲過程中，當(dāng)數(shù)據(jù)量非常少如只有不到50行記錄，數(shù)據(jù)占的頁面也不會超過1個(gè)頁面，那么使用表變量是一個(gè)很好的解決方案。
作用域(scope)
表變量像局部變量(local variable)一樣，有著很窄的作用域，只能應(yīng)用于定義的函數(shù)、存儲過程或批處理內(nèi)。如，一個(gè)會話里面有幾個(gè)批處理，那么表變量只能作用在它定義所在的批處理范圍內(nèi)。其他的批處理無法再調(diào)用它。
e.g.在MSSMS新增一個(gè)查詢，編寫< 腳本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é)束點(diǎn) Select * From @1
< 腳本S9.>所在的查詢相當(dāng)于一個(gè)會話，”Go”描述的一個(gè)批處理的結(jié)束點(diǎn)。在”Go”之前定義的表變量，在”Go”之后調(diào)用是發(fā)生“必須聲明變量@1”的錯(cuò)誤提示。
臨時(shí)表與表變量不同，臨時(shí)表的作用域是當(dāng)前會話都有效，一直到會話結(jié)束或者臨時(shí)表被Drop的時(shí)候。也就是說可以跨當(dāng)前會話的幾個(gè)批處理范圍。
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é)束點(diǎn) Select * from #1
< 腳本S10.>中可以看出在”GO”前后都可以查詢到臨時(shí)表#1。
在描述臨時(shí)表與表變量的作用域時(shí)，有個(gè)地方要注意的是，當(dāng) sp_executesql 或 Execute 語句執(zhí)行字符串時(shí)，字符串將作為它的自包含批處理執(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.>中，當(dāng)執(zhí)行到”Execute(N'Select * From @1')”時(shí)候，同樣發(fā)生與< 腳本S9.>一樣的錯(cuò)誤提示“必須聲明變量@1”.
臨時(shí)表是可以在sp_executesql 或 Execute 語句執(zhí)行字符串中被調(diào)用。這里不再舉例子，如果你有所模糊可以參考< 腳本S11.>把表變量轉(zhuǎn)成臨時(shí)表測試下就能加深理解與記憶。
存儲位置
說到臨時(shí)表和表變量的存儲位置，我們可以看到有很多版本的說法，特別是表變量。有的說表變量數(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í)表有相似，都會在數(shù)據(jù)庫tempdb創(chuàng)建，使用到tempdb存儲空間。
e.g.< 腳本S12.>臨時(shí)表
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,向臨時(shí)表#1插入1條數(shù)據(jù)后，數(shù)據(jù)庫未使用空間為501.38MB,未使用空間變小了。再來看整個(gè)數(shù)據(jù)庫的數(shù)據(jù)(data)使用的空間變化,從552KB變成560KB,使用了一頁的數(shù)據(jù)空間(8kb)。這說明一點(diǎn)，臨時(shí)表，即使你只插入一條數(shù)據(jù)都會使用到數(shù)據(jù)庫tempdb的空間。也許會有人問，要是我只建臨時(shí)表#1,不插入數(shù)據(jù)，會如何。我們可以結(jié)果：
這里你會發(fā)現(xiàn)前后的空間大小不變，不過，不要認(rèn)為沒有使用到數(shù)據(jù)庫tempdb數(shù)據(jù)空間，當(dāng)你多用戶創(chuàng)建臨時(shí)表結(jié)構(gòu)的時(shí)候，你就會發(fā)現(xiàn)其實(shí)都會應(yīng)用到數(shù)據(jù)庫tempdb的空間。我這里創(chuàng)建了10個(gè)#1后的效果如：
相同的原理，我們使用類似的方法測試表變量的情況，發(fā)現(xiàn)結(jié)論是與臨時(shí)表的一致的，會使用到數(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.>中，我多寫了一個(gè)”GO”之后檢查空間大小的存儲過程sp_spaceused。這樣為了了更能體現(xiàn)表變量使用空間變化情況。從插入數(shù)據(jù)前和插入數(shù)據(jù)后的結(jié)果圖來看，表變量不僅在數(shù)據(jù)庫tempdb創(chuàng)建了表結(jié)構(gòu)#267ABA7A類似的這樣表，表變量也應(yīng)用到了數(shù)據(jù)庫tempdb的空間。不過這里注意一點(diǎn)就是在”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ù)庫分配的磁盤空間。我們在實(shí)際的環(huán)境中，發(fā)現(xiàn)臨時(shí)數(shù)據(jù)庫tempdb使用的磁盤空間越來越大，這是其中的原因之一。
其他
臨時(shí)表與表變量，還有其他的特征，如臨時(shí)表受事務(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”之后，臨時(shí)表#1沒有數(shù)據(jù)插入，而表變量@1還有一條數(shù)據(jù)存在。說明表變量不受”Rollback Tran”所影響。它的行為有類似于局部變量一樣。
另外SQL Server對表變量不保留任何的統(tǒng)計(jì)信息，因?yàn)槿绱?，我們在?shù)據(jù)量大的時(shí)候使用表變量，發(fā)現(xiàn)比臨時(shí)表要慢許多。前面在I/O開銷那里我們?nèi)∮幸粋€(gè)特殊的例子，這里不再舉例。
小結(jié)
無論如何，臨時(shí)表和表變量有各自的特征，有自己優(yōu)點(diǎn)和缺點(diǎn)。在不同的場景選擇它們靈活應(yīng)用。本文章是我對臨時(shí)表和表變量的一些認(rèn)識理解，可能有些地方說的不夠好或者遺漏，你可以留言或Email與我聯(lián)系，我會繼續(xù)改進(jìn)或糾正，我也不希望有些錯(cuò)誤的見解會誤導(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
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。
關(guān)注官方微信