臨時表
臨時表與永久表相似，只是它的創建是在Tempdb中，它只有在一個數據庫連接結束后或者由SQL命令DROP掉，才會消失，否則就會一直存在。臨時表在創建的時候都會產生SQL Server的系統日志，雖它們在Tempdb中體現，是分配在內存中的，它們也支持物理的磁盤，但用戶在指定的磁盤里看不到文件。
臨時表分為本地和全局兩種，本地臨時表的名稱都是以“#”為前綴，只有在本地當前的用戶連接中才是可見的，當用戶從實例斷開連接時被刪除。全局臨時表的名稱都是以“##”為前綴，創建后對任何用戶都是可見的，當所有引用該表的用戶斷開連接時被刪除。
臨時表可以創建索引，也可以定義統計數據，所以可以用數據定義語言(DDL)的聲明來阻止臨時表添加的限制，約束，并參照完整性，如主鍵和外鍵約束。
臨時表在創建之后可以修改許多已定義的選項，包括：
1)添加、修改、刪除列。例如，列的名稱、長度、數據類型、精度、小數位數以及為空性均可進行修改，只是有一些限制而已。
2)可添加或刪除主鍵和外鍵約束。
3)可添加或刪除 UNIQUE 和 CHECK 約束及 DEFAULT 定義(對象)。
4)可使用 IDENTITY 或 ROWGUIDCOL 屬性添加或刪除標識符列。雖然 ROWGUIDCOL 屬性也可添加至現有列或從現有列刪除，但是任何時候在表中只能有一列可具有該屬性。
5)表及表中所選定的列已注冊為全文索引。
比較臨時表及表變量都可以通過SQL的選擇、插入、更新及刪除語句，它們的的不同主要體現在以下這些：
　　1)表變量是存儲在內存中的，當用戶在訪問表變量的時候，SQL Server是不產生日志的，而在臨時表中是產生日志的;
　　2)在表變量中，是不允許有非聚集索引的;
　　3)表變量是不允許有DEFAULT默認值，也不允許有約束;
　　4)臨時表上的統計信息是健全而可靠的，但是表變量上的統計信息是不可靠的;
　　5)臨時表中是有鎖的機制，而表變量中就沒有鎖的機制。

表變量
基本原則:能用表變量就用表變量.實在不行才使用臨時表
表變量主要是開銷系統的內存,而臨時表則使用tempdb.對于小數據量的中間數據存儲,可以使用表變量,而當需要臨時保存的數據很大時,建議使用臨時表.
　　表變量創建的語法類似于臨時表，區別就在于創建的時候，必須要為之命名。表變量是變量的一種，表變量也分為本地及全局的兩種，本地表變量的名稱都是以“@”為前綴，只有在本地當前的用戶連接中才可以訪問。全局的表變量的名稱都是以“@@”為前綴，一般都是系統的全局變量，像我們常用到的，如 @@Error代表錯誤的號，@@RowCount代表影響的行數。
1、為什么要使用表變量
表變量是從2000開始引入的，微軟認為與本地臨時表相比，表變量具有如下優點：
a.與其他變量的定義一樣，表變量具有良好的定義范圍，并會被自動清除；
b.在存儲過程中使用表變量會減少存儲過程重新編譯的發生；
c.表變量需要更少的鎖請求和日志資源；
d.可以在表變量上使用UDF,UDDT,XML。
2、表變量的限制
與臨時表相比，表變量存在著如下缺點：
a.在表變量上沒有統計信息，查詢優化器根據固定的預估值來選擇執行計劃，在數據很多的情況下，會導致查詢優化器選擇很差的執行計劃；
b.不能直接在表變量上創建索引，但可以通過創建約束（主鍵、唯一）來建立索引；
c.在DECLARE后，不能再對表變量進行更改；
d.不能對表變量執行INSERT EXEC , SELECT INTO語句（只針對05前的版本）；
e.不能通過EXEC或sp_executesql來執行牽涉到表變量的動態SQL語句，但如果表變量是在動態SQL語句內定義的，則可以。
3、那什么時候可以使用表變量
要使用表變量應該根據如下規則來判斷：
a.表的行數；
b.使用表變量能夠減少的重新編譯次數；
c.查詢的類型和對索引或者統計信息的依賴程度；
d.需要生用UDF,UDDT,XML的時候。
其實也就說，得從實際出發，根據具體的查詢，作出具體的選擇。但是，其中很關鍵的一點，如果表的行數非常多，使用表變量其實是更費資源的。 有人提出了這樣的建議：對于行數較少的情況下(小于1000行）可以使用表變量；如果行數很多（有幾萬行），則使用臨時表。
因此，在實際的開發中，應通過分別使用臨時表或表變量進行對比后，才作出決定。
4、使用表變量的誤區
對于表變量，很多人認為，表變量和其他變量一樣，只存在內存中，其實這是不正確的，表變量也存在tempdb中。可以通過下面例子進行對比。

      
        CREATE
      
      
        TABLE
      
       #TempTable ( TT_Col1 
      
        INT
      
      
         )


      
      
        DECLARE
      
      
        @TableVariable
      
      
        TABLE
      
       ( TV_Col1 
      
        INT
      
      
         )




      
      
        SELECT
      
      
        TOP
      
      
        2
      
      
        *
      
      
        FROM
      
      
            tempdb.sys.objects


      
      
        ORDER
      
      
        BY
      
       create_date 
      
        DESC
      
      
        DROP
      
      
        TABLE
      
       #TempTable
    

?

5、其他
由于表變量作用域有限，并且不是持久數據庫的一部分，因而不受事務回滾的影響。
表變量不受rollback影響，某些情況下會破壞數據的完整性。

      
        CREATE
      
      
        TABLE
      
       #TempTable ( TT_Col1 
      
        INT
      
      
         )


      
      
        DECLARE
      
      
        @TableVariable
      
      
        TABLE
      
       ( TV_Col1 
      
        INT
      
      
         )


      
      
        INSERT
      
      
          #TempTable


      
      
        VALUES
      
        ( 
      
        1
      
      
         )


      
      
        INSERT
      
      
        @TableVariable
      
      
        VALUES
      
        ( 
      
        1
      
      
         )


      
      
        BEGIN
      
      
        TRANSACTION
      
      
        INSERT
      
      
          #TempTable


      
      
        VALUES
      
        ( 
      
        2
      
      
         )


      
      
        INSERT
      
      
        @TableVariable
      
      
        VALUES
      
        ( 
      
        2
      
      
         )


      
      
        ROLLBACK
      
      
        SELECT
      
      
        *
      
      
        FROM
      
      
            #TempTable


      
      
        SELECT
      
      
        *
      
      
        FROM
      
      
        @TableVariable
      
      
        DROP
      
      
        TABLE
      
       #TempTable
    

?

【T-SQL系列】臨時表、表變量