以下的文章主要是淺談SQL Server臨時(shí)表與SQL Server表變量，在SQL Server數(shù)據(jù)庫(kù)的性能調(diào)優(yōu)中，其存在一個(gè)不可比擬的問(wèn)題就那是如何在一段需要長(zhǎng)時(shí)間的代碼或被頻繁調(diào)用的代碼中處理臨時(shí)數(shù)據(jù)集?表變量和SQL Server臨時(shí)表是兩種選擇。

記得在給一家國(guó)內(nèi)首屈一指的海運(yùn)公司作SQL Server應(yīng)用性能評(píng)估和調(diào)優(yōu)的時(shí)候就看到過(guò)大量的臨時(shí)數(shù)據(jù)集處理需求，而他們的開(kāi)發(fā)人員就無(wú)法確定什么時(shí)候用臨時(shí)表，什么時(shí)候用表變量，因此他們就簡(jiǎn)單的使用了臨時(shí)表。實(shí)際上SQL Server臨時(shí)表和表變量都有特定的適用環(huán)境。

先賣(mài)弄一些基礎(chǔ)的知識(shí)：

表變量

變量都以@或@@為前綴，表變量是變量的一種，另外一種變量被稱(chēng)為標(biāo)量(可以理解為標(biāo)準(zhǔn)變量，就是標(biāo)準(zhǔn)數(shù)據(jù)類(lèi)型的變量，例如整型int或者日期型DateTime)。以@前綴的表變量是本地的，因此只有在當(dāng)前用戶(hù)會(huì)話中才可以訪問(wèn)，而@@前綴的表變量是全局的，通常都是系統(tǒng)變量，比如說(shuō)@@error代表最近的一個(gè)T-SQL語(yǔ)句的報(bào)錯(cuò)號(hào)。

當(dāng)然因?yàn)楸碜兞渴紫仁莻€(gè)變量，因此它只能在一個(gè)Batch中生存，也就是我們所說(shuō)的邊界，超出了這個(gè)邊界，表變量也就消亡了。

表變量存放在內(nèi)存中，正是因?yàn)檫@一點(diǎn)所有用戶(hù)訪問(wèn)表變量的時(shí)候SQL Server是不需要生成日志。同時(shí)變量是不需要考慮其他會(huì)話訪問(wèn)的問(wèn)題，因此也不需要鎖機(jī)制，對(duì)于非常繁忙的系統(tǒng)來(lái)說(shuō)，避免鎖的使用可以減少一部分系統(tǒng)負(fù)載。

表變量另外還有一個(gè)限制就是不能創(chuàng)建索引，當(dāng)然也不存在統(tǒng)計(jì)數(shù)據(jù)的問(wèn)題，因此在用戶(hù)訪問(wèn)表變量的時(shí)候也就不存在執(zhí)行計(jì)劃選擇的問(wèn)題了(也就是以為著編譯階段后就沒(méi)有優(yōu)化階段了)，這一特性有的時(shí)候是件好事，而有些時(shí)候卻會(huì)造成一些麻煩。

臨時(shí)表

臨時(shí)對(duì)象都以#或##為前綴，SQL Server臨時(shí)表是臨時(shí)對(duì)象的一種，還有例如臨時(shí)存儲(chǔ)過(guò)程、臨時(shí)函數(shù)之類(lèi)的臨時(shí)對(duì)象，臨時(shí)對(duì)象都存儲(chǔ)在tempdb中。以#前綴的臨時(shí)表為本地的，因此只有在當(dāng)前用戶(hù)會(huì)話中才可以訪問(wèn)，而##前綴的臨時(shí)表是全局的，因此所有用戶(hù)會(huì)話都可以訪問(wèn)。

臨時(shí)表以會(huì)話為邊界，只要?jiǎng)?chuàng)建臨時(shí)表的會(huì)話沒(méi)有結(jié)束，臨時(shí)表就會(huì)持續(xù)存在，當(dāng)然用戶(hù)在會(huì)話中可以通過(guò)DROP TABLE命令提前銷(xiāo)毀臨時(shí)表。

我們前面說(shuō)過(guò)臨時(shí)表存儲(chǔ)在tempdb中，因此臨時(shí)表的訪問(wèn)是有可能造成物理IO的，當(dāng)然在修改時(shí)也需要生成日志來(lái)確保一致性，同時(shí)鎖機(jī)制也是不可缺少的。

跟表變量另外一個(gè)顯著去別就是SQL Server臨時(shí)表可以創(chuàng)建索引，也可以定義統(tǒng)計(jì)數(shù)據(jù)，因此SQL Server在處理訪問(wèn)臨時(shí)表的語(yǔ)句時(shí)需要考慮執(zhí)行計(jì)劃優(yōu)化的問(wèn)題。

表變量 vs. 臨時(shí)表

結(jié)論

綜上所述，大家會(huì)發(fā)現(xiàn)SQL Server臨時(shí)表和表變量在底層處理機(jī)制上是有很多差別的。

簡(jiǎn)單地總結(jié)，我們對(duì)于較小的臨時(shí)計(jì)算用數(shù)據(jù)集推薦使用表變量。如果數(shù)據(jù)集比較大，如果在代碼中用于臨時(shí)計(jì)算，同時(shí)這種臨時(shí)使用永遠(yuǎn)都是簡(jiǎn)單的全數(shù)據(jù)集掃描而不需要考慮什么優(yōu)化，比如說(shuō)沒(méi)有分組或分組很少的聚合(比如說(shuō)COUNT、SUM、AVERAGE、MAX等)，也可以考慮使用表變量。

使用表變量另外一個(gè)考慮因素是應(yīng)用環(huán)境的內(nèi)存壓力，如果代碼的運(yùn)行實(shí)例很多，就要特別注意內(nèi)存變量對(duì)內(nèi)存的消耗。

一般對(duì)于大的數(shù)據(jù)集我們推薦使用SQL Server臨時(shí)表，同時(shí)創(chuàng)建索引，或者通過(guò)SQL Server的統(tǒng)計(jì)數(shù)據(jù)(Statisitcs)自動(dòng)創(chuàng)建和維護(hù)功能來(lái)提供訪問(wèn)SQL語(yǔ)句的優(yōu)化。如果需要在多個(gè)用戶(hù)會(huì)話間交換數(shù)據(jù)，當(dāng)然臨時(shí)表就是唯一的選擇了。需要提及的是，由于臨時(shí)表存放在tempdb中，因此要注意tempdb的調(diào)優(yōu)。

【編輯推薦】

1. SQL Server 2005鏡像的概念與其工作模式
2. SQL Server置疑的出現(xiàn)的原因，現(xiàn)象與破解
3. SQL Server所有表的列信息展示
4. SQL Server ASP代碼與Access ASP代碼的比較
5. SQL Server內(nèi)存占用的執(zhí)行緩存的正確優(yōu)化