表變數和暫存資料表

      表變數儲存在記憶體中，而暫存資料表儲存在tempdb中，會涉及到物理IO讀寫，那麼我們是否可以由此得出結論，使用表變數要比使用暫存資料表效率高呢？相信有一部分人會和我有同樣的想法，使用表變數的效率高，真是如此嗎？先從一次最佳化預存程序的經曆說起。

      預存程序涉及到兩個表，一個是使用者今日積分表@tableUserScore（資料來源來自使用者積分詳情表中的今日資料)，一個是使用者積分統計表UserScoreSum，該預存程序邏輯就是統計@tableUserScore中使用者不同原因的積分值，產生到表UserScoreSum中。資料量不算很大，@tableUserScore中大概40萬條，但這個預存程序執行時間卻有些驚人，通常都在1個小時之上。最佳化的最終結果是將表變數@tabeUserScore換成了暫存資料表#tableUserScore，並在userid和reason上添加了聯合索引，最佳化的效果是執行時間控制在了40S左右。暫存資料表和表變數效率相差百倍，這次最佳化經曆讓我對暫存資料表和表變數有了重新認識，也有了一連串的疑問，它們是如何儲存的，效率如何，如何選用？

表結構

declare @tableUserScore table(
    userid int, --使用者編號
    name varchar(10), --使用者姓名
    reason varchar(32), --積分原因
    score int --積分值
)

create table UserScoreSum(
    userid int, --使用者編號
    name varchar(10), --使用者姓名
    createTime datetime, --時間
    reason1Score int, --原因1積分值
    reason2Score int, --原因2積分值
    reason3Score int, --原因3積分值
    reason4Score int, --原因4積分值
)

     以下是個人翻閱資料後的理解，總結出來希望能給和我有同樣認識的人提個醒，起到拋磚引玉的作用，也希望大家對理解錯誤之處提出指正。

     暫存資料表

     暫存資料表有兩種類型：本地表和全域表。在與首次建立或參考資料表時相同的 SQL Server 執行個體串連期間，本地暫存資料表只對於建立者是可見的。當使用者與 SQL Server 執行個體中斷連線後，將刪除本地暫存資料表。全域暫存資料表在建立後對任何使用者和任何串連都是可見的，當引用該表的所有使用者都與 SQL Server 執行個體中斷連線後，將刪除全域暫存資料表。本地暫存資料表的名稱都是以“#”為首碼，全域暫存資料表的名稱都是以“##”為首碼。

     暫存資料表儲存在tempdb中，因此暫存資料表的訪問是有可能造成物理IO的，當然在修改時也需要組建記錄檔來確保一致性，同時鎖機制也是不可缺少的。

     暫存資料表可以建立索引，也可以定義統計資料，所以可以用資料定義語言 (Data Definition Language)(DDL)的聲明來阻止暫存資料表添加的限制，約束，並參照完整性，如主鍵和外鍵約束。

     表變數

     表變數是變數的一種，表變數也分為本地及全域的兩種，本地表變數的名稱都是以“@”為首碼，只有在本地當前的使用者串連中才可以訪問。全域的表變數的名稱都是以“@@”為首碼，一般都是系統的全域變數，像我們常用到的，如@@Error代表錯誤的號，@@RowCount代表影響的行數。

     表變數存放在記憶體中，正是因為這一點所有使用者訪問表變數的時候SQL Server是不需要組建記錄檔。同時變數是不需要考慮其他會話訪問的問題，因此也不需要鎖機制，對於非常繁忙的系統來說，避免鎖的使用可以減少一部分系統負載。[表變數存放在記憶體是有一定限制的，如果表變數資料量超過閾值，會把記憶體耗盡，然後使用TempDB的空間，這樣主要還是使用硬碟空間，但同時把記憶體基本耗盡，增加了記憶體調入調出的機會，反而降低速度]

     表變數另外還有一個限制就是不能建立索引，當然也不存在統計資料的問題，因此在使用者訪問表變數的時候也就不存在執行計畫選擇的問題了(也就是以為著編譯階段後就沒有最佳化階段了)，這一特性有的時候是件好事，而有些時候卻會造成一些麻煩。

     暫存資料表 vs. 表變數

     1.儲存位置：暫存資料表是利用了硬碟(tempdb資料庫) ，表名變數是佔用記憶體，因此小資料量當然是記憶體中的表變數更快。當大資料量時，就不能用表變數了，太耗記憶體了。大資料量時適合用暫存資料表。

     2.效能：不能一概而論，表變數儲存資料有個效能臨界點，在這個臨界點之內，表變數比暫存資料表快，表變數是儲存在記憶體中的。

     3.索引：表變數不支援索引和統計資料，但可以有主鍵；暫存資料表則可以支援索引和統計資料。

     我們對於較小的臨時計算用資料集考慮使用表變數。如果資料集比較大，如果在代碼中用於臨時計算，同時這種臨時使用永遠都是簡單的全資料集掃描而不需要考慮什麼最佳化，比如說沒有分組或分組很少的彙總(比如說COUNT、SUM、AVERAGE、MAX等)，也可以考慮使用表變數。使用表變數另外一個考慮因素是應用環境的記憶體壓力，如果代碼的運行執行個體很多，就要特別注意記憶體變數對記憶體的消耗。一般對於大的資料集我們最好使用暫存資料表，同時建立索引。