一直以來(lái)大家對(duì)臨時(shí)表與表變量的孰優(yōu)孰劣爭(zhēng)論頗多,一些技術(shù)群里的朋友甚至認(rèn)為表變量幾乎一無(wú)是處,比如無(wú)統(tǒng)計(jì)信息,不支持事務(wù)等等.但事實(shí)并非如此.這里我就臨時(shí)表與表變量做個(gè)對(duì)比,對(duì)于大多數(shù)人不理解或是有歧義的地方進(jìn)行詳細(xì)說(shuō)明.

注:這里只討論一般臨時(shí)表,對(duì)全局臨時(shí)表不做闡述.

生命周期

臨時(shí)表:會(huì)話中,proc中,或使用顯式drop

表變量:batch中

這里用簡(jiǎn)單的code說(shuō)明表變量作用域

DECLARE @t TABLE(i int) ----定義表變量@t  
 
SELECT *FROM @t        -----訪問(wèn)OK  
 
insert into @t select 1 -----插入數(shù)據(jù)OK  
 
select * from  @t      -------訪問(wèn)OK  
go                     -------結(jié)束批處理  
select * from @t       -------不在作用域出錯(cuò) 

注意:雖然說(shuō)sqlserver在定義表變量完成前不允許你使用定義的變量.但注意下面情況仍然可正常運(yùn)行!

if 'a'='b' 
begin 
DECLARE @t TABLE(i int)  
end 
SELECT *FROM @t        -----仍然可以訪問(wèn)！  

日志機(jī)制

臨時(shí)表與表變量都會(huì)記錄在tempdb中記錄日志

不同的是臨時(shí)表的活動(dòng)日志在事務(wù)完成前是不能截?cái)嗟?

這里應(yīng)注意的是由于表變量不支持truncate,所以完全清空對(duì)象結(jié)果集時(shí)臨時(shí)表有明顯優(yōu)勢(shì),而表變量只能delete

事務(wù)支持

臨時(shí)表:支持

表變量:不支持

我們通過(guò)簡(jiǎn)單的實(shí)例加以說(shuō)明

create table #t (i int)  
declare @t table(i int)  
 
BEGIN TRAN ttt  
insert into #t select 1  
insert into @t select 1  
SELECT * FROM #t  ------returns 1 rows  
SELECT * FROM @t  ------returns 1 rows  
ROLLBACK tran ttt  
 
SELECT * FROM #t    -------no rows  
SELECT * FROM @t    -------still 1 rows  
drop table #t       ----no use drop @t in session 

鎖機(jī)制(select)

臨時(shí)表 會(huì)對(duì)相關(guān)對(duì)象加IS(意向共享)鎖

表變量 會(huì)對(duì)相關(guān)對(duì)象加SCH-S(架構(gòu)共享)鎖(相當(dāng)于加了nolock hint)

可以看出雖說(shuō)鎖的影響范圍不同,但由于作用域都只是會(huì)話或是batch中,臨時(shí)表的IS鎖雖說(shuō)兼容性不如表變量的SCH-S但絕大多數(shù)情況基本無(wú)影響.

感興趣的朋友可以用TF1200測(cè)試

索引支持

臨時(shí)表 支持

表變量 條件支持(僅SQL2014)

沒(méi)錯(cuò),在sql2014中你可以在創(chuàng)建表的同時(shí)創(chuàng)建索引 圖1-1

注:在sql2014之前表變量只支持創(chuàng)建一個(gè)默認(rèn)的唯一性約束

cod

DECLARE @t TABLE   
(  
col1 int index inx_1 CLUSTERED,   
col2 int  index index_2 NONCLUSTERED,  
       index index_3 NONCLUSTERED(col1,col2)  
) 

圖1-1

CREATE FUNCTION TVP_Customers (@cust nvarchar(10))  
RETURNS TABLE 
AS 
 RETURN 
 (SELECT RowNum, CustomerID, OrderDate, ShipCountry  
 FROM BigOrders  
 WHERE CustomerID = @cust);  
GO  
CREATE FUNCTION TVF_Customers (@cust nvarchar(10))  
RETURNS @T TABLE (RowNum int, CustomerID nchar(10), OrderDate date,  
 ShipCountry nvarchar(30))  
AS 
BEGIN 
 INSERT INTO @T  
  SELECT RowNum, CustomerID, OrderDate, ShipCountry  
  FROM BigOrders  
  WHERE CustomerID = @cust  
  RETURN 
END;  
 
DBCC FREEPROCCACHE  
GO  
SELECT * FROM TVF_Customers('CENTC');  
GO  
SELECT * FROM TVP_Customers('CENTC');  
GO  
SELECT * FROM TVF_Customers('SAVEA');  
GO  
SELECT * FROM TVP_Customers('SAVEA');  
GO  
 
select b.text,a.execution_count,a.* from sys.dm_exec_query_stats a  
cross apply sys.dm_exec_sql_text(a.sql_handle) b  
where b.text like '%_Customers%' 

圖1-2

其它方面

表變量不支持select into，alter，truncate，dbcc等

表變量不支持table hint 如(force seek)

執(zhí)行計(jì)劃預(yù)估

我想這里可能是引起使用何種方式爭(zhēng)論比較突出的地方,由于表變量沒(méi)有統(tǒng)計(jì)信息,無(wú)法添加索引等使得大家對(duì)其在執(zhí)行計(jì)劃中的性能表現(xiàn)嗤之以鼻,但實(shí)際情況呢?我們需要深入分析.

關(guān)于臨時(shí)表的預(yù)估這里我就不做介紹了,主要對(duì)表變量的預(yù)估做詳細(xì)闡述.

表變量在sql2000引入的一個(gè)原因就是為了在一些執(zhí)行過(guò)程中減少重編譯.以獲得更好的性能.當(dāng)然帶來(lái)好處的同時(shí)也會(huì)帶來(lái)一定弊端.由于其不涉及重編譯,優(yōu)化器其實(shí)并不知道表變量中的具體行數(shù),此時(shí)他采取了保守的預(yù)估方式:預(yù)估行數(shù)為1行.如圖2-1

Code

declare @t table (i int)  
select * from @t-----此時(shí)0行預(yù)估行數(shù)為1行  
insert into @t select 1  
select * from @t-----此時(shí)1行,預(yù)估行數(shù)仍為1行  
insert into @t values (2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(14),(15),(16),(17),(18),(19),(20)  
select * from @t ----此時(shí)19行,預(yù)估行數(shù)仍為1行  
 
--....無(wú)論實(shí)際@t中有多少行,由于沒(méi)有重編譯,預(yù)估均為1行 

圖2-1

 所以當(dāng)我們加上重編譯的的操作,此時(shí)優(yōu)化器就知道了表變量的具體行數(shù).如圖2-2

Code

declare @t table (i int)  
select * from @t option(recompile)-----此時(shí)0行預(yù)估行數(shù)為1行  
insert into @t select 1  
select * from @t  option(recompile)-----此時(shí)1行,預(yù)估行數(shù)為1行  
insert into @t values (2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(14),(15),(16),(17),(18),(19),(20)  
select * from @t  option(recompile)----此時(shí)19行,預(yù)估行數(shù)為19行  
--....當(dāng)加入重編譯hint時(shí),優(yōu)化器就知道的表變量的行數(shù). 

圖2-2

至此,我們可以看到優(yōu)化器知道了表變量中的行數(shù).這樣在表變量掃描的過(guò)程中,尤其針對(duì)數(shù)據(jù)量較大的情形,不會(huì)因?yàn)轭A(yù)估總是1而引起一些問(wèn)題.

如果你剛知道這里的預(yù)估原理,現(xiàn)有的代碼都加上重編譯那工作量可想而知了..這里介紹一個(gè)新的跟蹤標(biāo)記,Trace Flag 2453.

TF2453可以一定程度上替代重編譯Hint,但只是在非簡(jiǎn)單計(jì)劃（trivial plans）的情形下

注:TF2453只在sql2012 SP2和SQL2014中的補(bǔ)丁中起作用

#p#

表變量謂詞預(yù)估

由于表變量木有統(tǒng)計(jì)信息,在優(yōu)化器知道整體行數(shù)的前提下將會(huì)根據(jù)謂詞的情形

采用不同的規(guī)則"猜"來(lái)進(jìn)行預(yù)估.

注:這里有些規(guī)則筆者未找到微軟相應(yīng)的算法文檔,經(jīng)過(guò)自己根據(jù)數(shù)據(jù)推算得出.

看到這里的朋友請(qǐng)為我點(diǎn)個(gè)贊J(很長(zhǎng)時(shí)間推算得出.可能數(shù)學(xué)忘得差不多了)

注:由于檢索對(duì)象本身及為變量,謂詞為變量,或是常數(shù)無(wú)影響

常見謂詞下預(yù)估算法:

a ">", "<" 運(yùn)算符 按照表變量數(shù)據(jù)量的30%進(jìn)行預(yù)估

b "like" 運(yùn)算符 按照表變量數(shù)據(jù)量的10%進(jìn)行預(yù)估

c "=" 運(yùn)算符 按照表變量數(shù)據(jù)量的0.75次方預(yù)估

實(shí)例如圖2-3

code

declare @i int 
set @i=13  
DECLARE @T TABLE(I INT);  
INSERT INTO @T VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(14),(15),(16),(17),(18),(19),(20)  
------表變量中存在個(gè)數(shù)字  
select * from @T where I < 1  option(recompile) ------20*30% 預(yù)估數(shù)為6  
select * from @T where I > @i option(recompile) --------20*30%預(yù)估數(shù)為6  
select * from @T where I like @i  option(recompile) --------20*10% 預(yù)估數(shù)為2  
select * from @T where I like 1  option(recompile)  --------20*10 預(yù)估數(shù)為2  
select * from @T where I = @i  option(recompile) --------POWER(20.00000,0.75) 預(yù)估數(shù)為9.45742  
select * from @T where I = 1  option(recompile)  --------POWER(20.00000,0.75) 預(yù)估數(shù)為9.45742  
 
insert into @T  
select DatabaseLogID from AdventureWorks2008R2.dbo.DatabaseLog------insert new records  
select * from @T option(recompile) ------------此時(shí)數(shù)據(jù)為行  
select * from @T where I = 1  option(recompile)--------------------POWER(1617.00000,0.75) 預(yù)估數(shù)為254.99550 

圖2-3

可以看出根據(jù)不同的謂詞優(yōu)化器會(huì)采用不同的預(yù)估方式,雖然它不如統(tǒng)計(jì)信息下的密度,直方圖等來(lái)的精確(尤其是等值預(yù)估,在數(shù)據(jù)量巨大的情形下,其效果可能接近統(tǒng)計(jì)信息),但在了解數(shù)據(jù)的前提下如果適合表變量我們還是可以大膽使用的.

Tempdb競(jìng)爭(zhēng)

tempdb的競(jìng)爭(zhēng)本身涵蓋的知識(shí)面比較大,這里我們只討論臨時(shí)表與表變量的孰優(yōu)孰劣.

通過(guò)前面的介紹我們知道臨時(shí)表是支持事務(wù)的,而表變量時(shí)不支持的.正因如此很多人放棄了表變量的使用.但任何事情都有兩方面,支持就一定好嗎?由于臨時(shí)表對(duì)事務(wù)的支持,在高并發(fā)的情形中可能正因?yàn)槠涫聞?wù)的支持造成系統(tǒng)表鎖,總而影響并發(fā).

我們通過(guò)一個(gè)簡(jiǎn)單的實(shí)例來(lái)說(shuō)明

日常管理中,我發(fā)現(xiàn)很多開發(fā)人員在使用臨時(shí)表時(shí)采用select * into #t from …的語(yǔ)法,這樣的寫法如果數(shù)據(jù)量稍大,將會(huì)造成事務(wù)持有系統(tǒng)表鎖的時(shí)間變長(zhǎng),從而影響并發(fā),吞吐.我們通過(guò)一個(gè)簡(jiǎn)單的實(shí)例說(shuō)明.如圖3-1

Code 我們通過(guò)sqlquerystress模擬并發(fā)

----SSMS測(cè)試數(shù)據(jù)  
Use tempdb  
create table t  
( id int identity,str1 char(8000))----more pages for many records  
 
insert into t select 'a' 
go 100  
 
----sqlquerystress  
select * into #t  
from t----57s  
 
----sqlquerystress  
declare @t table 
( id int,str1 char(8000))  
insert into @t  
select * from t-----1s 

圖3-1

通過(guò)圖3-1可以看出上述情形中臨時(shí)表簡(jiǎn)直不堪重負(fù).臨時(shí)表與表變量到底該如何應(yīng)用不是看誰(shuí)比誰(shuí)的優(yōu)點(diǎn)多,應(yīng)視具體情形而定

結(jié)語(yǔ):借用火影忍者中宇智波. 鼬的一句名言：”任何術(shù)都是有缺陷的” 同樣,在數(shù)據(jù)庫(kù)的世界里沒(méi)有哪項(xiàng)技術(shù)是完美無(wú)缺的.根據(jù)實(shí)際的場(chǎng)景,情形,選擇合理的實(shí)現(xiàn)方式才是我們的初衷.

原文出自：http://www.cnblogs.com/shanksgao/p/3988089.html