在T-SQL中使用臨時(shí)表的注意事項(xiàng)
一、臨時(shí)表的兩種表現(xiàn)形式。
在SQL Server數(shù)據(jù)庫(kù)中，臨時(shí)表主要有兩種形式，分別為全局臨時(shí)表與局部臨時(shí)表。這兩種表有很大的不同，主要體現(xiàn)在名字上、可見(jiàn)性上以及可用性上。具體來(lái)說(shuō)，本地臨時(shí)表的名字是以#符號(hào)開(kāi)頭的;而全局臨時(shí)表則是以##兩個(gè)#字符號(hào)開(kāi)頭。從可見(jiàn)性上來(lái)說(shuō)，局部臨時(shí)表是有當(dāng)前用戶(hù)創(chuàng)建的，并且只有當(dāng)前用戶(hù)的會(huì)話才可以訪問(wèn)。而對(duì)于全局表來(lái)說(shuō)，只要這個(gè)臨時(shí)表存在，那么用戶(hù)創(chuàng)建會(huì)話后對(duì)所有的用戶(hù)都是可見(jiàn)的。兩者在刪除的時(shí)機(jī)尚也有不同。如本地臨時(shí)表在當(dāng)前用戶(hù)中斷會(huì)話后這個(gè)臨時(shí)表就會(huì)被刪除。而全局臨時(shí)表只有當(dāng)飲用這個(gè)表的用戶(hù)從數(shù)據(jù)庫(kù)中斷開(kāi)連接時(shí)才會(huì)被刪除。由于這兩種臨時(shí)表存在這么多的差異，數(shù)據(jù)庫(kù)管理員就需要根據(jù)實(shí)際應(yīng)用來(lái)確定采用合適的臨時(shí)表類(lèi)型。
筆者現(xiàn)在以一個(gè)實(shí)際的例子來(lái)談?wù)勂胀ū?、本地臨時(shí)表、全局臨時(shí)表三個(gè)表的差異。如現(xiàn)在有一個(gè)保存員工信息的表user。這個(gè)表是一個(gè)普通表，只要其建立就不會(huì)自動(dòng)刪除，任何好在數(shù)據(jù)庫(kù)中有使用這個(gè)表(具有訪問(wèn)權(quán)限)的用戶(hù)都可以訪問(wèn)這個(gè)表，除非這個(gè)表被所有者刪除或者更改了權(quán)限。在用戶(hù)A(具有訪問(wèn)權(quán)限)訪問(wèn)這個(gè)表的過(guò)程中，數(shù)據(jù)庫(kù)可能會(huì)根據(jù)需要生成一張本地臨時(shí)表#user。此時(shí)只有這個(gè)會(huì)話才可以訪問(wèn)這個(gè)本地臨時(shí)表。當(dāng)這個(gè)用戶(hù)的會(huì)話中斷之后，這個(gè)本地臨時(shí)表也會(huì)被自動(dòng)刪除。不過(guò)根據(jù)需要，數(shù)據(jù)庫(kù)也可能會(huì)建立全局臨時(shí)表##user(在名字上與本地臨時(shí)表不同)。此時(shí)數(shù)據(jù)庫(kù)中的任何用戶(hù)只要連接到了數(shù)據(jù)庫(kù)就可以訪問(wèn)這個(gè)全局臨時(shí)表(訪問(wèn)權(quán)限上的不同)。當(dāng)這個(gè)創(chuàng)建臨時(shí)表會(huì)話的用戶(hù)中斷數(shù)據(jù)庫(kù)連接時(shí)，這個(gè)臨時(shí)表是否會(huì)刪除是一個(gè)未知數(shù)，這要看當(dāng)時(shí)的實(shí)際情況(在可用性上不同)。如果此時(shí)還有其他用戶(hù)連接在這個(gè)表上的話，那么這個(gè)全局臨時(shí)表就不會(huì)被刪除。只有在中斷連接時(shí)，沒(méi)有其他用戶(hù)在訪問(wèn)這個(gè)表時(shí)，即某個(gè)用戶(hù)(不一定是創(chuàng)建這張全局臨時(shí)表的用戶(hù))斷開(kāi)連接并且所有其他的會(huì)話不再使用這個(gè)表時(shí)才會(huì)被刪除。
可見(jiàn)無(wú)論是全局臨時(shí)表還是本地臨時(shí)表，其跟普通表相比，最重要的一個(gè)差異就是其會(huì)根據(jù)需要自動(dòng)創(chuàng)建。當(dāng)不再需要時(shí)其又會(huì)自動(dòng)刪除。這也正是臨時(shí)表的魅力所在，其可以在數(shù)據(jù)處理的過(guò)程中，減少很多中間表格。
二、使用臨時(shí)表的好處。
在T-SQL語(yǔ)言中使用臨時(shí)表的好處是很顯而易見(jiàn)的。筆者下面就舉一些常見(jiàn)的好處。
如利用臨時(shí)表來(lái)組織數(shù)據(jù)，比普通表會(huì)更加的簡(jiǎn)潔、緊湊。這主要是在臨時(shí)表中可以實(shí)現(xiàn)很多的特性。如可以進(jìn)行預(yù)處理計(jì)算。如當(dāng)發(fā)現(xiàn)基本標(biāo)中的索引不怎么合適，也可以在數(shù)據(jù)庫(kù)臨時(shí)表中重新創(chuàng)建索引以?xún)?yōu)化原有的索引。特別是當(dāng)需要多次訪問(wèn)某個(gè)表或者視圖的時(shí)候，利用臨時(shí)表來(lái)組織數(shù)據(jù)是一個(gè)提高效率的好方法。即使只是一個(gè)簡(jiǎn)單的查詢(xún)，其效率的提升也是很明顯的。為此，使用臨時(shí)表最明顯的一個(gè)好處就似乎可以提高數(shù)據(jù)庫(kù)的性能，特別是查詢(xún)的性能。
另外使用臨時(shí)表還可以減少中間表的產(chǎn)生。在進(jìn)行某些操作時(shí)，本來(lái)往往需要一些中間表的幫助才可以完成。而現(xiàn)在數(shù)據(jù)庫(kù)管理員可以讓數(shù)據(jù)庫(kù)在需要時(shí)自動(dòng)生成中間表，并在用完后進(jìn)行自動(dòng)刪除。如此的話，中間表的建立與刪除就不需要數(shù)據(jù)庫(kù)管理員人為的管理了。所以，使用臨時(shí)表可以減少數(shù)據(jù)庫(kù)系統(tǒng)中的垃圾表，也可以降低用戶(hù)的工作量。為此筆者認(rèn)為，臨時(shí)表是SQL Server數(shù)據(jù)庫(kù)中一個(gè)很使用的工具。作為數(shù)據(jù)庫(kù)管理員，要在平時(shí)的工作中，合理使用這個(gè)臨時(shí)表，發(fā)揮其最大的效用。雖然針對(duì)特定的任務(wù)該采用什么類(lèi)型的臨時(shí)表，有很多容易混淆的地方。但是數(shù)據(jù)庫(kù)管理員不能夠因噎廢食，而應(yīng)該積極的去嘗試。
三、要對(duì)本地臨時(shí)表特別引起重視。
在平時(shí)的應(yīng)用與管理中，本地臨時(shí)表的應(yīng)用幾率要比全局臨時(shí)表多的多。而且本地臨時(shí)表由于只有用戶(hù)自己的會(huì)話可以進(jìn)行訪問(wèn)，而全局臨時(shí)表則是所有用戶(hù)都可以訪問(wèn)。為此在安全性上本地臨時(shí)表也要比全局臨時(shí)表高的多。為此筆者認(rèn)為數(shù)據(jù)庫(kù)管理員主要要掌握本地臨時(shí)表的應(yīng)用技巧。然后再對(duì)比的去了解全局臨時(shí)表的信息，這可能是學(xué)習(xí)SQLServer數(shù)據(jù)庫(kù)臨時(shí)表的一個(gè)捷徑。
對(duì)于本地臨時(shí)表來(lái)說(shuō)，需要注意在不同情形下應(yīng)用本地臨時(shí)表其刪除的實(shí)際。如假設(shè)數(shù)據(jù)庫(kù)在執(zhí)行一個(gè)存儲(chǔ)過(guò)程的時(shí)候建立了本地臨時(shí)表。那么此時(shí)這個(gè)本地臨時(shí)表并不是在會(huì)話終止的時(shí)候自動(dòng)刪除，而是在這個(gè)存儲(chǔ)過(guò)程執(zhí)行完畢后就會(huì)刪除。這是什意思呢?也就是說(shuō)，用戶(hù)發(fā)起的某個(gè)會(huì)話，為了執(zhí)行一個(gè)特殊的作業(yè)(如用戶(hù)的這個(gè)會(huì)話調(diào)用了某個(gè)存儲(chǔ)過(guò)程)。此時(shí)其實(shí)就是會(huì)話再創(chuàng)建一個(gè)子會(huì)話的過(guò)程。在這種情況下需要注意的是，子會(huì)話創(chuàng)建的本地臨時(shí)表只在子會(huì)話內(nèi)部有效。當(dāng)這個(gè)子會(huì)話終止的時(shí)候(存儲(chǔ)過(guò)程執(zhí)行完畢)，此時(shí)這個(gè)臨時(shí)表就會(huì)自動(dòng)刪除。即對(duì)于調(diào)用這個(gè)子會(huì)話的會(huì)話來(lái)說(shuō)，這個(gè)其子會(huì)話的創(chuàng)建的臨時(shí)表對(duì)于其也是無(wú)效的，因?yàn)榕R時(shí)表已經(jīng)在子會(huì)話關(guān)閉的時(shí)候自動(dòng)刪除。做一個(gè)形象的比喻。即現(xiàn)在做父親的去叫兒子造一座房子。當(dāng)兒子死亡的時(shí)候，這座房子也會(huì)消失。對(duì)于這種情況，數(shù)據(jù)庫(kù)管理員需要注意。父會(huì)話只能夠引用子會(huì)話從臨時(shí)表中傳遞出來(lái)的數(shù)據(jù)。也就是說(shuō)，父會(huì)話要訪問(wèn)子會(huì)話創(chuàng)建的臨時(shí)表的數(shù)據(jù)，只有一種手段。即先讓子會(huì)話對(duì)臨時(shí)表中的數(shù)據(jù)進(jìn)行查詢(xún)或者操作，然后把結(jié)構(gòu)回傳給父會(huì)話。父會(huì)話是不能夠直接訪問(wèn)子會(huì)話所創(chuàng)建的臨時(shí)表。當(dāng)然這個(gè)限制是專(zhuān)門(mén)針對(duì)本地臨時(shí)表而言的。對(duì)于全局臨時(shí)表來(lái)說(shuō)，本身就是所有用戶(hù)都可以訪問(wèn)，為此就沒(méi)有這個(gè)限制。
四、臨時(shí)表對(duì)日志與鎖的影響。
日志文件是數(shù)據(jù)庫(kù)中很重要的一個(gè)工具。無(wú)論是SQL Server數(shù)據(jù)庫(kù)還是Oracle數(shù)據(jù)庫(kù)，都有日志這個(gè)工具。如憑借重做日志工具，數(shù)據(jù)庫(kù)管理員可以在數(shù)據(jù)庫(kù)故障的時(shí)候借此來(lái)恢復(fù)數(shù)據(jù)，將數(shù)據(jù)恢復(fù)到故障的那個(gè)點(diǎn)上。但是在使用臨時(shí)表的時(shí)候，需要注意一點(diǎn)，就是臨時(shí)表不會(huì)有日志文件。即對(duì)臨時(shí)表進(jìn)行的DML等操作不會(huì)形成日志文件。這個(gè)特性即有好處，也有壞處。好處是對(duì)于臨時(shí)表的更改不會(huì)保存到日志文件中。也就是說(shuō)，如果數(shù)據(jù)庫(kù)發(fā)生了故障，則保存在臨時(shí)表中的數(shù)據(jù)是不能夠恢復(fù)的。為此數(shù)據(jù)庫(kù)管理員不得不重新執(zhí)行某些作業(yè)以重新生成臨時(shí)表中的數(shù)據(jù)。好處就是對(duì)于臨時(shí)表的DML操作速度會(huì)非常的塊。除了其他的原因?qū)е缕湫阅艿奶嵘?，在更改其?nèi)容時(shí)不會(huì)生成日志信息也是一個(gè)重要的原因。為此對(duì)臨時(shí)表的操作不生成日志信息，這是一個(gè)雙刃劍。數(shù)據(jù)庫(kù)管理員在日常工作中，要盡量發(fā)揮其優(yōu)勢(shì)，減少其負(fù)面作用的影響。
另外，若采用臨時(shí)表這種處理機(jī)制的話，還需要注意其對(duì)鎖的影響。在介紹本地臨時(shí)表與全局臨時(shí)表差異的時(shí)候，筆者就介紹過(guò)，本地臨時(shí)表只對(duì)當(dāng)前的會(huì)話有效。即使當(dāng)前會(huì)話又創(chuàng)建了另外一個(gè)子會(huì)話，也只對(duì)子會(huì)話有效。當(dāng)某個(gè)會(huì)話終止的時(shí)候，這臨時(shí)表就會(huì)自動(dòng)被刪除。而對(duì)于普通表或者全局臨時(shí)表來(lái)說(shuō)，可能同時(shí)多個(gè)會(huì)話都可以訪問(wèn)這個(gè)表。這兩者有什么區(qū)別呢?若允許多個(gè)會(huì)話可以同時(shí)訪問(wèn)某個(gè)表的話，那么這個(gè)表就可能會(huì)遇到鎖的情況。即某個(gè)用戶(hù)會(huì)話在對(duì)表中地記錄進(jìn)行DML等操作時(shí)，為了保證數(shù)據(jù)的一致性，會(huì)對(duì)相關(guān)的記錄進(jìn)行加鎖等措施。而采用本地臨時(shí)表的話，由于只有一個(gè)會(huì)話可以訪問(wèn)臨時(shí)表中的數(shù)據(jù)，所以即使這個(gè)會(huì)話更改臨時(shí)表中的數(shù)據(jù)，也不會(huì)有鎖沖突的問(wèn)題。故其在更改本地臨時(shí)表中的數(shù)據(jù)時(shí)，就不用為其加鎖。所以，對(duì)于本地臨時(shí)表的操作速度就要比其他表來(lái)的快。故在何時(shí)的情況下使用臨時(shí)表無(wú)疑可以提高數(shù)據(jù)庫(kù)的整體性能。如可以將一些操作在臨時(shí)表中完成，然后再將最后的結(jié)果更新到基本表中。
【編輯推薦】
