臨時表或表變量我們一般用來充當(dāng)中間結(jié)果集，很多時候都在用，但真正了解他們之間的區(qū)別的人還是很少的，網(wǎng)上流傳的說法也不甚統(tǒng)一，所以今天我就做一個實(shí)驗(yàn)，讓我們看看臨時表和表變量的區(qū)別，以及他們各自的用途。

執(zhí)行以下語句，對測試環(huán)境做準(zhǔn)備

DBCC DROPCLEANBUFFERS --從緩沖池中刪除所有清除緩沖區(qū) DBCC FREEPROCCACHE --清除計劃緩存 CHECKPOINT --寫入MDF中

1) 關(guān)于存儲

表變量在內(nèi)存中，是否真的不寫磁盤，不會造成任何IO開銷？

use tempdb exec sp_spaceused--database_name database_size unallocated space --tempdb 8.50 MB 6.75 MB--tempdb數(shù)據(jù)庫占用8.50M，未用空間6.75Muse TestDBCREATE TABLE #Table3(id int, AtypeId char(1024))declare @count int = 50 INSERT INTO #Table3(id, atypeid) SELECT TOP(@count) 1 as id, 'sss' FROM GraspFZDRPWrite001.dbo.BillType --隨便寫的一張表,只是讓其能循環(huán)插入50條記錄use tempdb CHECKPOINT --寫入MDF中exec sp_spaceused--database_name database_size unallocated space --tempdb 8.50 MB 6.62 MB

運(yùn)行代碼，我們發(fā)現(xiàn)，unallocated space 未用空間減小了，從6.75M減少至6.62M，說明臨時表是占用了tempdb空間的，這點(diǎn)毋庸置疑。

我們接著看表變量又是如何？

use tempdb exec sp_spaceused--database_name database_size unallocated space --tempdb 8.50 MB 6.69 MBuse TestDBdeclare @Table3 table (id int, atypeid char(1024)) declare @count int = 50INSERT INTO @Table3(id, atypeid) SELECT TOP(@count) 1 as id, 'sss' as atypeid FROM GraspFZDRPWrite001.dbo.BillType use tempdb checkpointexec sp_spaceused --database_name database_size unallocated space --tempdb 8.50 MB 6.62 MB

unallocated space值再次變小，說明此操作存在占用tempdb的數(shù)據(jù)庫空間。兩者其實(shí)都存儲在tempdb中，都占用tempdb的數(shù)據(jù)庫空間。

2)對表變量記錄的操作是否占用更少的LOG

我們首先看臨時表插入

if OBJECT_ID('tempdb..#T') is not null drop table #Tdeclare @b1 bigint, @b2 bigintCREATE TABLE #T (s char(128))SELECT @b1=num_of_bytes_written from sys.dm_io_virtual_file_stats(2, 2) declare @i int = 0 while @i<20000 BEGINinsert into #T select '臨時表：原值'set @i=@i+1 ENDuse tempdb checkpoint select @b2=num_of_bytes_written from sys.dm_io_virtual_file_stats(2, 2) select @b2-@b1 as 日志增量 --經(jīng)測試，臨時表日志增量 4851712

然后是表變量插入

use TestDBdeclare @b1 bigint, @b2 bigint declare @V table (s char(128)) select @b1=num_of_bytes_written from sys.dm_io_virtual_file_stats(2, 2) declare @i int = 0 while @i<20000 begininsert into @V select '表變量：原值'set @i=@i+1 end use tempdb checkpoint select @b2=num_of_bytes_written from sys.dm_io_virtual_file_stats(2, 2) select @b2-@b1 as 日志增量 --經(jīng)測試，表變量日志增量5007360

兩者日志記錄相差不多，表變量還比臨時表的日志寫入更多！

3)Lock上的不同表現(xiàn)

--臨時表 if OBJECT_ID('tempdb..#T') is not null drop table #Tcreate table #T (s varchar(128)) insert into #T select '臨時表：原值'execute sp_lock @@spid --查看當(dāng)前用戶進(jìn)程的會話 所在的鎖關(guān)系BEGIN TRANSACTIONupdate #T set s= '臨時表：被更新'execute sp_lock @@spid --發(fā)現(xiàn)增加了一個排他鎖 ROLLBACK TRANSACTIONexecute sp_lock @@spid --排他鎖被釋放GO--表變量 declare @V table (s char(128)) insert into @V select '表變量：原值'execute sp_lock @@spidBEGIN TRANSACTIONupdate @V set s='表變量：被更新'execute sp_lock @@spid ROLLBACK TRANSACTIONexecute sp_lock @@spid --并沒有在事務(wù)中加任何鎖

臨時表的更新會加鎖，表變量更新不會

4)事務(wù)處理中的不同

if OBJECT_ID('tempdb..#T') is not null drop table #Tcreate table #T (s varchar(128)) declare @T table (s varchar(128)) insert into #T select '臨時表：原值' insert into @T select '表變量：原值'BEGIN TRANSACTIONupdate #T set s='臨時表：被更新'update @T set s='表變量：被更新' ROLLBACK TRANSACTIONselect * from #T select * from @T

結(jié)果發(fā)現(xiàn)，臨時表得值被回滾，表變量并沒有回滾。可以得出，表變量不受事務(wù)影響。

5)UDF中的不同

--表變量 CREATE FUNCTION dbo.example1 ( ) RETURNS INT AS BEGIN DECLARE @t1 TABLE (i INT) INSERT @t1 VALUES(1) INSERT @t1 VALUES(2) UPDATE @t1 SET i = i + 5 DELETE @t1 WHERE i < 7 DECLARE @max INT SELECT @max = MAX(i) FROM @t1 RETURN @max END GO; --臨時表 CREATE FUNCTION dbo.example2 ( ) RETURNS INT AS BEGIN CREATE TABLE #t1 (i INT) INSERT #t1 VALUES(1) INSERT #t1 VALUES(2) UPDATE #t1 SET i = i + 5 DELETE #t1 WHERE i < 7 DECLARE @max INT SELECT @max = MAX(i) FROM #t1 RETURN @max END GO --物理表 CREATE FUNCTION dbo.example3 ( ) RETURNS INT AS BEGIN CREATE TABLE table1 ( id INT IDENTITY, name VARCHAR(32) ) INSERT table1(name) VALUES('aaron') RETURN SCOPE_IDENTITY() END

運(yùn)行后，可以發(fā)現(xiàn)，函數(shù)內(nèi)無法訪問臨時表，也無法創(chuàng)建實(shí)體表，同理也無法更新新增刪除實(shí)體表的記錄，但可以使用表變量來進(jìn)行運(yùn)算和操作

6) 性能對比

if OBJECT_ID('tempdb..#T') is not null drop table #T create table #T (s char(1024)) declare @i int = 0 while @i<100000 begininsert into #T select '臨時表：原值'set @i=@i+1 END SELECT * FROM #T --3秒左右 --3秒 godeclare @V table (s char(1024)) declare @i int = 0 while @i<100000 begininsert into @V select '表變量：原值'set @i=@i+1 END SELECT * FROM @V --3秒左右，和臨時表幾乎一樣

在插入性能上，兩者基本一致

7)關(guān)聯(lián)操作上，性能的不同表現(xiàn)

--構(gòu)造數(shù)據(jù) dbcc dropcleanbuffers; --從緩沖池中刪除所有清除緩沖區(qū)use TestDBif object_id('tempdb..#temp') is not null drop table #temp SELECT IDENTITY(INT, 1, 1) as _rowid, a.ppt, a.GoodsId INTO #temp FROM GraspFZDRPWrite001.dbo.Goods a --goods表是一個有記錄16049的數(shù)據(jù)表GO--用臨時表 SELECT * FROM #temp WHERE _rowid IN (SELECT max(_rowid) FROM #temp GROUP BY Ppt) --很快 --用表變量 DECLARE @PDTEMP TABLE (_rowid int, ppt CHAR(1), goodsid INT) INSERT INTO @PDTEMP SELECT * FROM #tempSELECT * FROM @PDTEMP WHERE _rowid IN (SELECT max(_rowid) FROM @PDTEMP GROUP BY ppt) go --相當(dāng)慢，記錄幾乎出不來

為啥會出現(xiàn)以上的結(jié)果呢，我們跟蹤執(zhí)行計劃可以發(fā)現(xiàn)，前者執(zhí)行計劃選擇的哈希匹配，后者則是相當(dāng)緩慢的嵌套循環(huán)。

分析原因，因?yàn)榫酆喜僮鲿帽淼慕y(tǒng)計信息來聚合，表變量沒有統(tǒng)計信息，系統(tǒng)默認(rèn)只能選擇嵌套循環(huán)，而這導(dǎo)致嚴(yán)重的慢查詢的主要原因

我們強(qiáng)制查詢使用hash join連接(哈希匹配)

DECLARE @PDTEMP TABLE (_rowid int, ppt CHAR(1), goodsid INT) INSERT INTO @PDTEMP SELECT * FROM #tempSELECT * FROM @PDTEMP WHERE _rowid IN (SELECT max(_rowid) FROM @PDTEMP GROUP BY ppt) option(hash join)

這時速度跟用臨時表一樣，但不推薦這樣使用，因?yàn)橐坏┻@樣強(qiáng)制使用，SQLSERVER的自動優(yōu)化則不會起作用

8)把存儲過程中返回的數(shù)據(jù)集插入到臨時表 用于保存存儲過程中返回的數(shù)據(jù)集

CREATE TABLE #sp_who3 ( SPID INT, Status VARCHAR(32) NULL ) gocreate procedure pWho ASselect 1 as spid, 'Tomas' as statusunion allselect 1 as spid, 'Viviy' as status goinsert #sp_who3 execute pWhoselect * from #sp_who3DECLARE @PDTEMP TABLE (SPID INT, Status VARCHAR(32) NULL) INSERT @PDTEMP EXEC pWho SELECT * FROM @PDTEMPGO

兩者都可以正常使用，但是有一點(diǎn)必須注意，對于2008以前的版本，表變量是不支持這樣操作的

9)是否可以動態(tài)的生成列

--臨時表 SELECT * INTO #TEMP_objects FROM sys.objects; --成功執(zhí)行--表變量 SELECT * INTO @PDTEMP FROM sys.objects; --報錯

表變量不能生成動態(tài)列，因?yàn)楸碜兞恳坏﹦?chuàng)建，他的架構(gòu)就是固定的，而臨時表可以更改架構(gòu)甚至是索引

10)生命期是否相同

if OBJECT_ID('tempdb..#temp_foo') is not null drop table #temp_foo--臨時表 DECLARE @sql VARCHAR(8000) SET @sql = 'Create TABLE #temp_foo (a INT,b INT,c INT) Insert into #temp_foo values(1,1,1) ' EXEC(@sql) INSERT #temp_foo SELECT 1,2,3 SELECT * FROM #temp_foo GO--表變量 DECLARE @sql VARCHAR(8000) SET @sql = 'DECLARE @foo TABLE(a INT,b INT,c INT) Insert into @foo values(1,1,1)' EXEC(@sql) INSERT @foo SELECT 1,2,3 Go;

同時存在兩個不同的批處理，外面的批處理要調(diào)用里面的批處理聲明的表，臨時表和表變量均不能使用

--臨時表 DECLARE @sql VARCHAR(8000) Create TABLE #temp_foo (a INT,b INT,c INT) SET @sql = 'Insert into #temp_foo values(1,1,1)' EXEC(@sql) INSERT #temp_foo SELECT 1,2,3 SELECT * FROM #temp_foo GO --可以執(zhí)行--表變量 DECLARE @sql VARCHAR(8000) DECLARE @foo TABLE(a INT,b INT,c INT) SET @sql = 'Insert into @foo values(1,1,1)' EXEC(@sql) INSERT @foo SELECT 1,2,3 SELECT * FROM @foo Go --執(zhí)行到EXEC(@SQL)報錯，找不到@foo

臨時表在跨批處理中，里面的批處理可以調(diào)用到外面聲明的表，表變量卻不行(實(shí)體表只要是聲明在前，都可以被調(diào)用)

總結(jié)： 無表關(guān)聯(lián)操作，只作為中間集進(jìn)行數(shù)據(jù)處理，建議用表變量；有表關(guān)聯(lián)，且不能確定數(shù)據(jù)量大小的情況下，建議用臨時表。

總結(jié)

以上是生活随笔為你收集整理的临时表与表变量深入探究的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。