【SQL Server】Temp Table 與 Table Variable的比較筆記

寫Store Procedure的時候,我們常常會需要建立暫時的資料表來協助我們處理資料,一般而言我們會有兩種選項(以SQL Server2000後來說):Temp Table與Table Variable。
而什麼時候要使用哪個,之前筆者其實一直想好好了解一下,這篇文章的內容來自就這個主題下去查詢後得到的內容,若有錯誤請協助指正,本篇內容更偏向個人統整筆記。

我們先來看這個表格,是我歸納各文章中的大多數的內容:

                               Temp Table | Table Variable
tempdb的交易紀錄檔 小 | 大
可否加Index與Default Constraint 可 | 否
Transaction Rollback 可 | 否

這個表是最基本的比較,然而在我看的另一篇文章中有指出不同的看法,上面這些敘述在基本面上是正確的,但是論到根本的實做的話可能又有些不同。

下列再逐一列出一些被提到的論點

  • Temp Table是可以如一般Table一樣使用DDL語句
  • SQL Server會幫Temp Table建立統計數據(Statistics),意味著QO(Query Optimizer)可以選擇適合的計畫,相對來說SQL Server並不會對Table Variable建立統計數據,Recomplie次數會更少
  • 兩者都會寫下交易日誌(Transcation Log),
  • 對大量資料的推薦,一般會建議使用Temp Table,可以吃到Index的好處,另外需要確認Tempdb中的分配空間,避免預設空間過小的問題
    (註:兩者的實例化都是在Tempdb,)
  • 當Store Procedure A 建立了一個Temp Table後,若呼叫Store Procedure B,在B中是可以使用該Temp Table的

其中這篇文章表示(這篇文末也是我上面說有探討根本情況下的一些比較討論,有興趣可以看看),根據作者的測試(內文有它的測試數據),在100,000行以下的行數兩者的效能會差不多,然而隨著資料表的增大,Temp Table的表現會明顯的比Table Variable好。

這個結論跟我看的大多數文章是一致的,當資料集大的時候,會推薦使用Temp Table,小資料集的時候可以依據當下的實作狀況來評估兩者的使用,效能上十分小的話Table Variable甚至會小超一點執行效能,然而隨著資料集的增大,會慢慢的反超,所以在撰寫Store Procedure時應注意操作的資料集對象的量級來評估,若隨著資料源的成長,也要適時評估適合的寫法。