臨時(shí)表

??? 在深入臨時(shí)表之前，我們首先需要討論一下會(huì)話(Session),一個(gè)會(huì)話僅僅是一個(gè)客戶(hù)端到數(shù)據(jù)引擎的連接。在SQL Server Management Studio(SSMS)中，每一個(gè)查詢(xún)窗口都會(huì)和數(shù)據(jù)庫(kù)引擎建立連接。一個(gè)應(yīng)用程序可以和數(shù)據(jù)庫(kù)建立一個(gè)或多個(gè)連接，除此之外，應(yīng)用程序還可能建立連接后一直不釋放直到應(yīng)用程序結(jié)束，也可能使用完釋放連接需要時(shí)建立連接。

??? 那么，什么是臨時(shí)表？在 BOL (CREATE TABLE) 中，我們可以知道臨時(shí)表和以Create table語(yǔ)句創(chuàng)建的表有著相同的物理構(gòu)成，但臨時(shí)表與正常的表不同之處有:

?

1) 臨時(shí)表的名字不能超過(guò)116個(gè)字符，這是由于數(shù)據(jù)庫(kù)引擎為了辨別不同會(huì)話建立不同的臨時(shí)表，所以會(huì)自動(dòng)在臨時(shí)表的名字后附加一串

?

2) 局部臨時(shí)表（以“#”開(kāi)頭命名的）作用域僅僅在當(dāng)前的連接內(nèi)，從在存儲(chǔ)過(guò)程中建立局部臨時(shí)表的角度來(lái)看，局部臨時(shí)表會(huì)在下列情況被Drop:

??? a.顯式調(diào)用DROP Table語(yǔ)句

??? b.當(dāng)局部臨時(shí)表在存儲(chǔ)過(guò)程內(nèi)被創(chuàng)建時(shí)，存儲(chǔ)過(guò)程結(jié)束也就意味著局部臨時(shí)表被DROP

??? c.當(dāng)前會(huì)話結(jié)束，在會(huì)話內(nèi)創(chuàng)建的所有局部臨時(shí)表都會(huì)被Drop

?

3) 全局臨時(shí)表（以“##”開(kāi)頭命名的）在所有的會(huì)話內(nèi)可見(jiàn)，所以在創(chuàng)建全局臨時(shí)表之前首先檢查其是否存在，否則如果已經(jīng)存在，你將會(huì)得到重復(fù)創(chuàng)建對(duì)象的錯(cuò)誤.

??? a.全局臨時(shí)表會(huì)在創(chuàng)建其的會(huì)話結(jié)束后被DROP,其它會(huì)話將不能對(duì)全局臨時(shí)表進(jìn)行引用。

??? b.引用是在語(yǔ)句級(jí)別進(jìn)行，比如說(shuō)下面例子:

??????? i.建立新的查詢(xún)窗口，運(yùn)行如下語(yǔ)句:

    
           create
    
    
      table
    
     ##temp (RowID 
    
      int
    
    )
  

??????? ii.再次開(kāi)啟一個(gè)新的查詢(xún)創(chuàng)建，使用如下語(yǔ)句每5秒中對(duì)全局臨時(shí)表進(jìn)行引用

    
           while
    
     1=1 
    
      begin
    
    
      select
    
     * 
    
      from
    
     ##temp

       
    
      waitfor
    
     delay 
    
      '00:00:05'
    
    
      end
    
  

?????? iii.回到第一個(gè)窗口，關(guān)閉窗口

?????? iv.在下一個(gè)循環(huán)引用全局臨時(shí)表時(shí)，將產(chǎn)生錯(cuò)誤

?

4) 不能對(duì)臨時(shí)表進(jìn)行分區(qū)。

?

5) 不能對(duì)臨時(shí)表加外鍵約束

6) 臨時(shí)表內(nèi)列的數(shù)據(jù)類(lèi)型不能定義成沒(méi)有在TempDb中沒(méi)有定義自定義數(shù)據(jù)類(lèi)型(自定義數(shù)據(jù)類(lèi)型是數(shù)據(jù)庫(kù)級(jí)別的對(duì)象，而臨時(shí)表屬于TempDb),由于TempDb在每次SQL Server重啟后會(huì)被自動(dòng)創(chuàng)建，所以你必須使用 startup stored procedure 來(lái)為T(mén)empDb創(chuàng)建自定義數(shù)據(jù)類(lèi)型。你也可以通過(guò)修改Model數(shù)據(jù)庫(kù)來(lái)達(dá)到這一目標(biāo)。

?

7) XML列不能定義成XML集合的形式，除非這個(gè)集合已經(jīng)在TempDb中定義

?

??? 臨時(shí)表既可以通過(guò)Create Table語(yǔ)句創(chuàng)建，也可以通過(guò)”SELECT <select_list> INTO #table”語(yǔ)句創(chuàng)建。你還可以針對(duì)臨時(shí)表使用”INSERT INTO #table EXEC stored_procedure”這樣的語(yǔ)句。

??? 臨時(shí)表可以擁有命名的約束和索引。但是，當(dāng)兩個(gè)用戶(hù)在同一時(shí)間調(diào)用同一存儲(chǔ)過(guò)程時(shí)，將會(huì)產(chǎn)生”There is already an object named ‘<objectname>’ in the database”這樣的錯(cuò)誤。所以最好的做法是不用為建立的對(duì)象進(jìn)行命名，而使用系統(tǒng)分配的在TempDb中唯一的。6

??? 參考6 談?wù)摿撕芏嘤捎谂R時(shí)表而導(dǎo)致的存儲(chǔ)過(guò)程重編譯的原因以及避免的方法。

?

誤區(qū)

??? 誤區(qū)1.表變量?jī)H僅在內(nèi)存中。

??? 誤區(qū)2.臨時(shí)表僅僅存儲(chǔ)在物理介質(zhì)中

??? 這兩種觀點(diǎn)都是明顯的誤區(qū)，在參考1的Q4節(jié)。表變量都是在TempDb數(shù)據(jù)庫(kù)中創(chuàng)建，因?yàn)楸碜兞看鎯?chǔ)的數(shù)據(jù)有可能超過(guò)物理內(nèi)存。除此之外，我們發(fā)現(xiàn)只要內(nèi)存足夠，表變量和臨時(shí)表都會(huì)在內(nèi)存中創(chuàng)建和處理。它們也同樣可以在任何時(shí)間被存入磁盤(pán)。

??? 如何證明這點(diǎn)？請(qǐng)看下面代碼(在SQL Server 2000到2008中都有效)

      
        -- make a list of all of the user tables currently active in the
      
    

      
        -- TempDB database
      
    

      
        if
      
       object_id(
      
        'tempdb..#tempTables'
      
      ) 
      
        is
      
      
        not
      
      
        null
      
      
        drop
      
      
        table
      
       #tempTables
    

      
        select
      
       name 
      
        into
      
       #tempTables 
      
        from
      
       tempdb..sysobjects 
      
        where
      
       type =
      
        'U'
      
    

      
        -- prove that even this new temporary table is in the list.
      
    

      
        -- Note the suffix at the end of it to uniquely identify the table across sessions.
      
    

      
        select
      
       * 
      
        from
      
       #tempTables 
      
        where
      
       name 
      
        like
      
      
        '#tempTables%'
      
    

      
        GO
      
    

      
        -- create a table variable
      
    

      
        declare
      
       @MyTableVariable 
      
        table
      
       (RowID 
      
        int
      
      )
    

      
        -- show all of the new user tables in the TempDB database.
      
    

      
        select
      
       name 
      
        from
      
       tempdb..sysobjects
    

      
        where
      
       type =
      
        'U'
      
      
        and
      
       name 
      
        not
      
      
        in
      
       (
      
        select
      
       name 
      
        from
      
       #tempTables)
    

?

??? 還有一些“證明”臨時(shí)表僅僅存在于內(nèi)存中謬誤，下面我來(lái)指出其中一個(gè)：

??? 注意表變量的名字是系統(tǒng)分配的，表變量的第一個(gè)字符”@”并不是一個(gè)字母，所以它并不是一個(gè)有效的變量名。系統(tǒng)會(huì)在TempDb中為表變量創(chuàng)建一個(gè)系統(tǒng)分配的名稱(chēng)，所以任何在sysobjects或sys.tables查找表變量的方法都會(huì)失敗。

??? 正確的方法應(yīng)該是我前面例子中的方法，我看到很多人使用如下查詢(xún)查表變量：

     select * from sysobjects 
    
      where
    
     name like
    
      '#tempTables%'
    
  

??? 上述代碼看上去貌似很好用，但會(huì)產(chǎn)生多用戶(hù)的問(wèn)題。你建立兩個(gè)連接，在第一個(gè)連接中創(chuàng)建臨時(shí)表，在第二個(gè)窗口中運(yùn)行上面的語(yǔ)句能看到第一個(gè)連接創(chuàng)建的臨時(shí)表，如果你在第二個(gè)連接中嘗試操作這個(gè)臨時(shí)表，那么可能會(huì)產(chǎn)生錯(cuò)誤，因?yàn)檫@個(gè)臨時(shí)表不屬于你的會(huì)話。

?

誤區(qū)3.表變量不能擁有索引。

??? 這個(gè)誤區(qū)也同樣錯(cuò)誤。雖然一旦你創(chuàng)建一個(gè)表變量之后，就不能對(duì)其進(jìn)行DDL語(yǔ)句了，這包括Create Index語(yǔ)句。然而你可以在表變量定義的時(shí)候?yàn)槠鋭?chuàng)建索引)比如如下語(yǔ)句.

    
      declare
    
     @MyTableVariable 
    
      table
    
     (RowID intPRIMARY 
    
      KEY
    
    
      CLUSTERED
    
    ) 
  

??? 這個(gè)語(yǔ)句將會(huì)創(chuàng)建一個(gè)擁有聚集索引的表變量。由于主鍵有了對(duì)應(yīng)的聚集索引，所以一個(gè)系統(tǒng)命名的索引將會(huì)被創(chuàng)建在RowID列上。

??? 下面的例子演示你可以在一個(gè)表變量的列上創(chuàng)建唯一約束以及如何建立符合索引。

    
      declare
    
     @temp 
    
      TABLE
    
     (

   RowID 
    
      int
    
    
      NOT
    
    
      NULL
    
    ,

   ColA 
    
      int
    
    
      NOT
    
    
      NULL
    
    ,

   ColB 
    
      char
    
    (1)
    
      UNIQUE
    
    ,

   
    
      PRIMARY
    
    
      KEY
    
    
      CLUSTERED
    
    (RowID, ColA))
  

1) SQL 并不能為表變量建立統(tǒng)計(jì)信息，就像其能為臨時(shí)表建立統(tǒng)計(jì)信息一樣。這意味著對(duì)于表變量，執(zhí)行引擎認(rèn)為其只有1行，這也意味著針對(duì)表變量的執(zhí)行計(jì)劃并不是最優(yōu)。雖然估計(jì)的執(zhí)行計(jì)劃對(duì)于表變量和臨時(shí)表都為1，但是實(shí)際的執(zhí)行計(jì)劃對(duì)于臨時(shí)表會(huì)根據(jù)每次存儲(chǔ)過(guò)程的重編譯而改變（看參考1，Q2部分）.如果臨時(shí)表不存在，在生成執(zhí)行計(jì)劃的時(shí)候會(huì)產(chǎn)生錯(cuò)誤。

?

2) 前面提到，一定建立表變量后就無(wú)法對(duì)其進(jìn)行DDL語(yǔ)句操作。因此如果需要為表建立索引或者加一列，你需要臨時(shí)表。

?

3) 表變量不能使用select …into語(yǔ)句，而臨時(shí)表可以

?

4) 在SQL Server 2008中，你可以將表變量作為參數(shù)傳入存儲(chǔ)過(guò)程。但是臨時(shí)表不行。在SQL Server 2000和2005中表變量也不行。

?

5) 作用域:表變量?jī)H僅在當(dāng)前的批處理中有效,并且對(duì)任何在其中嵌套的存儲(chǔ)過(guò)程等不可見(jiàn)。局部臨時(shí)表只在當(dāng)前會(huì)話中有效，這也包括嵌套的存儲(chǔ)過(guò)程。但對(duì)父存儲(chǔ)過(guò)程不可見(jiàn)。全局臨時(shí)表可以在任何會(huì)話中可見(jiàn)，但是會(huì)隨著創(chuàng)建其的會(huì)話終止而DROP，其它會(huì)話這時(shí)就不能再引用全局臨時(shí)表。

?

6) 排序規(guī)則：表變量使用當(dāng)前數(shù)據(jù)庫(kù)的排序規(guī)則，臨時(shí)表使用TempDb的排序規(guī)則。如果它們不兼容，你還需要在查詢(xún)或者表定義中進(jìn)行指定(參考7. Table Variables and Temporary Tables )

?

7) 你如果希望在動(dòng)態(tài)SQL中使用表變量，你必須在動(dòng)態(tài)SQL中定義表變量。而臨時(shí)表可以提前定義，在動(dòng)態(tài)SQL中進(jìn)行引用。

?

說(shuō)了這么多，那么，我該如何選擇呢？

??? 微軟 推薦 使用表變量（看參考4），如果表中的行數(shù)非常小，則使用表變量。很多”網(wǎng)絡(luò)專(zhuān)家”會(huì)告訴你100是一個(gè)分界線,因?yàn)檫@是統(tǒng)計(jì)信息創(chuàng)建查詢(xún)計(jì)劃效率高低的開(kāi)始。但是我還是希望告訴你針對(duì)你的特定需求對(duì)臨時(shí)表和表變量進(jìn)行測(cè)試。很多人在自定義函數(shù)中使用表變量，如果你需要在表變量中使用主鍵和唯一索引，你會(huì)發(fā)現(xiàn)包含數(shù)千行的表變量也依然性能卓越。但如果你需要將表變量和其它表進(jìn)行join,你會(huì)發(fā)現(xiàn)由于不精準(zhǔn)的執(zhí)行計(jì)劃，性能往往會(huì)非常差。

??? 為了證明這點(diǎn)，請(qǐng)看本文的附件。附件中代碼創(chuàng)建了表變量和臨時(shí)表.并裝入了AdventureWorks數(shù)據(jù)庫(kù)的Sales.SalesOrderDetail表。為了得到足夠的測(cè)試數(shù)據(jù)，我將這個(gè)表中的數(shù)據(jù)插入了10遍。然后以ModifiedDate 列作為條件將臨時(shí)表和表變量與原始的Sales.SalesOrderDetail表進(jìn)行了Join操作，從統(tǒng)計(jì)信息來(lái)看IO差別顯著。從時(shí)間來(lái)看表變量做join花了50多秒，而臨時(shí)表僅僅花了8秒。

??? 如果你需要在表建立后對(duì)表進(jìn)行DLL操作，那么選擇臨時(shí)表吧。

??? 臨時(shí)表和表變量有很多類(lèi)似的地方。所以有時(shí)候并沒(méi)有具體的細(xì)則規(guī)定如何選擇哪一個(gè)。對(duì)任何特定的情況，你都需要考慮其各自?xún)?yōu)缺點(diǎn)并做一些性能測(cè)試。下面的表格會(huì)讓你比較其優(yōu)略有了更詳細(xì)的參考。

?

總結(jié)

?

參考:

1) INF: Frequently Asked Questions - SQL Server 2000 - Table Variables

2) T-SQL BOL (SQL 2000), table data type

3) T-SQL BOL (SQL 2008), Declare @local_variable

4) T-SQL BOL (SQL 2008), CREATE TABLE

5) Table-Valued Parameters (Database Engine)

6) Troubleshooting stored procedure recompilation

7) Local Temporary Tables and Table Variables

8) Startup stored procedure

9) Data Definition Language (DDL)

其它值得閱讀的文章:

1) Things You Didn’t Know About Temp Tables and Table Variables

?

?

?

-----------------------------------------------------------------------

原文鏈接: http://www.sqlservercentral.com/articles/Temporary+Tables/66720/

Translated by: CareySon

【譯】表變量和臨時(shí)表的比較