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

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

      
        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、其他
由于表變量作用域有限，并且不是持久數(shù)據(jù)庫的一部分，因而不受事務(wù)回滾的影響。
表變量不受rollback影響，某些情況下會(huì)破壞數(shù)據(jù)的完整性。

      
        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系列】臨時(shí)表、表變量