在T-SQL中使用臨時表的注意事項

一、臨時表的兩種表現形式。

在SQL Server資料庫中，臨時表主要有兩種形式，分別為全域性臨時表與區域性臨時表。這兩種表有很大的不同，主要體現在名字上、可見性上以及可用性上。具體來說，本地臨時表的名字是以#符號開頭的;而全域性臨時表則是以##兩個#字元號開頭。從可見性上來說，區域性臨時表是有當前使用者建立的，並且只有當前使用者的會話才可以訪問。而對於全域性表來說，只要這個臨時表存在，那麼使用者建立會話後對所有的使用者都是可見的。兩者在刪除的時機尚也有不同。如本地臨時表在當前使用者中斷會話後這個臨時表就會被刪除。而全域性臨時表只有當飲用這個表的使用者從資料庫中斷開連線時才會被刪除。由於這兩種臨時表存在這麼多的差異，資料庫管理員就需要根據實際應用來確定採用合適的臨時表型別。

筆者現在以一個實際的例子來談談普通表、本地臨時表、全域性臨時表三個表的差異。如現在有一個儲存員工資訊的表user。這個表是一個普通表，只要其建立就不會自動刪除，任何好在資料庫中有使用這個表(具有訪問許可權)的使用者都可以訪問這個表，除非這個表被所有者刪除或者更改了許可權。在使用者A(具有訪問許可權)訪問這個表的過程中，資料庫可能會根據需要生成一張本地臨時表#user。此時只有這個會話才可以訪問這個本地臨時表。當這個使用者的會話中斷之後，這個本地臨時表也會被自動刪除。不過根據需要，資料庫也可能會建立全域性臨時表##user(在名字上與本地臨時表不同)。此時資料庫中的任何使用者只要連線到了資料庫就可以訪問這個全域性臨時表(訪問許可權上的不同)。當這個建立臨時表會話的使用者中斷資料庫連線時，這個臨時表是否會刪除是一個未知數，這要看當時的實際情況(在可用性上不同)。如果此時還有其他使用者連線在這個表上的話，那麼這個全域性臨時表就不會被刪除。只有在中斷連線時，沒有其他使用者在訪問這個表時，即某個使用者(不一定是建立這張全域性臨時表的使用者)斷開連線並且所有其他的會話不再使用這個表時才會被刪除。

可見無論是全域性臨時表還是本地臨時表，其跟普通表相比，最重要的一個差異就是其會根據需要自動建立。當不再需要時其又會自動刪除。這也正是臨時表的魅力所在，其可以在資料處理的過程中，減少很多中間表格。

二、使用臨時表的好處。

在T-SQL語言中使用臨時表的好處是很顯而易見的。筆者下面就舉一些常見的好處。

如利用臨時表來組織資料，比普通表會更加的簡潔、緊湊。這主要是在臨時表中可以實現很多的特性。如可以進行預處理計算。如當發現基本標中的索引不怎麼合適，也可以在資料庫臨時表中重新建立索引以優化原有的索引。特別是當需要多次訪問某個表或者檢視的時候，利用臨時表來組織資料是一個提高效率的好方法。即使只是一個簡單的查詢，其效率的提升也是很明顯的。為此，使用臨時表最明顯的一個好處就似乎可以提高資料庫的效能，特別是查詢的效能。

另外使用臨時表還可以減少中間表的產生。在進行某些操作時，本來往往需要一些中間表的幫助才可以完成。而現在資料庫管理員可以讓資料庫在需要時自動生成中間表，並在用完後進行自動刪除。如此的話，中間表的建立與刪除就不需要資料庫管理員人為的管理了。所以，使用臨時表可以減少資料庫系統中的垃圾表，也可以降低使用者的工作量。為此筆者認為，臨時表是SQL Server資料庫中一個很使用的工具。作為資料庫管理員，要在平時的工作中，合理使用這個臨時表，發揮其最大的效用。雖然針對特定的任務該採用什麼型別的臨時表，有很多容易混淆的地方。但是資料庫管理員不能夠因噎廢食，而應該積極的去嘗試。

三、要對本地臨時表特別引起重視。

在平時的應用與管理中，本地臨時表的應用機率要比全域性臨時表多的多。而且本地臨時表由於只有使用者自己的會話可以進行訪問，而全域性臨時表則是所有使用者都可以訪問。為此在安全性上本地臨時表也要比全域性臨時表高的多。為此筆者認為資料庫管理員主要要掌握本地臨時表的應用技巧。然後再對比的去了解全域性臨時表的資訊，這可能是學習SQLServer資料庫臨時表的一個捷徑。

對於本地臨時表來說，需要注意在不同情形下應用本地臨時表其刪除的實際。如假設資料庫在執行一個儲存過程的時候建立了本地臨時表。那麼此時這個本地臨時表並不是在會話終止的時候自動刪除，而是在這個儲存過程執行完畢後就會刪除。這是什意思呢?也就是說，使用者發起的某個會話，為了執行一個特殊的作業(如使用者的這個會話呼叫了某個儲存過程)。此時其實就是會話再建立一個子會話的過程。在這種情況下需要注意的是，子會話建立的本地臨時表只在子會話內部有效。當這個子會話終止的時候(儲存過程執行完畢)，此時這個臨時表就會自動刪除。即對於呼叫這個子會話的會話來說，這個其子會話的建立的臨時表對於其也是無效的，因為臨時表已經在子會話關閉的時候自動刪除。做一個形象的比喻。即現在做父親的去叫兒子造一座房子。當兒子死亡的時候，這座房子也會消失。對於這種情況，資料庫管理員需要注意。父會話只能夠引用子會話從臨時表中傳遞出來的資料。也就是說，父會話要訪問子會話建立的臨時表的資料，只有一種手段。即先讓子會話對臨時表中的資料進行查詢或者操作，然後把結構回傳給父會話。父會話是不能夠直接訪問子會話所建立的臨時表。當然這個限制是專門針對本地臨時表而言的。對於全域性臨時表來說，本身就是所有使用者都可以訪問，為此就沒有這個限制。

四、臨時表對日誌與鎖的影響。

日誌檔案是資料庫中很重要的一個工具。無論是SQL Server資料庫還是Oracle資料庫，都有日誌這個工具。如憑藉重做日誌工具，資料庫管理員可以在資料庫故障的時候藉此來恢復資料，將資料恢復到故障的那個點上。但是在使用臨時表的時候，需要注意一點，就是臨時表不會有日誌檔案。即對臨時表進行的DML等操作不會形成日誌檔案。這個特性即有好處，也有壞處。好處是對於臨時表的更改不會儲存到日誌檔案中。也就是說，如果資料庫發生了故障，則儲存在臨時表中的資料是不能夠恢復的。為此資料庫管理員不得不重新執行某些作業以重新生成臨時表中的資料。好處就是對於臨時表的DML操作速度會非常的塊。除了其他的原因導致其效能的提升外，在更改其內容時不會生成日誌資訊也是一個重要的原因。為此對臨時表的操作不生成日誌資訊，這是一個雙刃劍。資料庫管理員在日常工作中，要儘量發揮其優勢，減少其負面作用的影響。

另外，若採用臨時表這種處理機制的話，還需要注意其對鎖的影響。在介紹本地臨時表與全域性臨時表差異的時候，筆者就介紹過，本地臨時表只對當前的會話有效。即使當前會話又建立了另外一個子會話，也只對子會話有效。當某個會話終止的時候，這臨時表就會自動被刪除。而對於普通表或者全域性臨時表來說，可能同時多個會話都可以訪問這個表。這兩者有什麼區別呢?若允許多個會話可以同時訪問某個表的話，那麼這個表就可能會遇到鎖的情況。即某個使用者會話在對錶中地記錄進行DML等操作時，為了保證資料的一致性，會對相關的記錄進行加鎖等措施。而採用本地臨時表的話，由於只有一個會話可以訪問臨時表中的資料，所以即使這個會話更改臨時表中的資料，也不會有鎖衝突的問題。故其在更改本地臨時表中的資料時，就不用為其加鎖。所以，對於本地臨時表的操作速度就要比其他表來的快。故在何時的情況下使用臨時表無疑可以提高資料庫的整體效能。如可以將一些操作在臨時表中完成，然後再將最後的結果更新到基本表中。