臨時表

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

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

?

1) 臨時表的名字不能超過116個字符，這是由于數據庫引擎為了辨別不同會話建立不同的臨時表，所以會自動在臨時表的名字后附加一串

?

2) 局部臨時表（以“#”開頭命名的）作用域僅僅在當前的連接內，從在存儲過程中建立局部臨時表的角度來看，局部臨時表會在下列情況被Drop:

??? a.顯式調用DROP Table語句

??? b.當局部臨時表在存儲過程內被創建時，存儲過程結束也就意味著局部臨時表被DROP

??? c.當前會話結束，在會話內創建的所有局部臨時表都會被Drop

?

3) 全局臨時表（以“##”開頭命名的）在所有的會話內可見，所以在創建全局臨時表之前首先檢查其是否存在，否則如果已經存在，你將會得到重復創建對象的錯誤.

??? a.全局臨時表會在創建其的會話結束后被DROP,其它會話將不能對全局臨時表進行引用。

??? b.引用是在語句級別進行，比如說下面例子:

??????? i.建立新的查詢窗口，運行如下語句:

    
           create
    
    
      table
    
     ##temp (RowID 
    
      int
    
    )
  

??????? ii.再次開啟一個新的查詢創建，使用如下語句每5秒中對全局臨時表進行引用

    
           while
    
     1=1 
    
      begin
    
    
      select
    
     * 
    
      from
    
     ##temp

       
    
      waitfor
    
     delay 
    
      '00:00:05'
    
    
      end
    
  

?????? iii.回到第一個窗口，關閉窗口

?????? iv.在下一個循環引用全局臨時表時，將產生錯誤

?

4) 不能對臨時表進行分區。

?

5) 不能對臨時表加外鍵約束

6) 臨時表內列的數據類型不能定義成沒有在TempDb中沒有定義自定義數據類型(自定義數據類型是數據庫級別的對象，而臨時表屬于TempDb),由于TempDb在每次SQL Server重啟后會被自動創建，所以你必須使用 startup stored procedure 來為TempDb創建自定義數據類型。你也可以通過修改Model數據庫來達到這一目標。

?

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

?

??? 臨時表既可以通過Create Table語句創建，也可以通過”SELECT <select_list> INTO #table”語句創建。你還可以針對臨時表使用”INSERT INTO #table EXEC stored_procedure”這樣的語句。

??? 臨時表可以擁有命名的約束和索引。但是，當兩個用戶在同一時間調用同一存儲過程時，將會產生”There is already an object named ‘<objectname>’ in the database”這樣的錯誤。所以最好的做法是不用為建立的對象進行命名，而使用系統分配的在TempDb中唯一的。6

??? 參考6 談論了很多由于臨時表而導致的存儲過程重編譯的原因以及避免的方法。

?

誤區

??? 誤區1.表變量僅僅在內存中。

??? 誤區2.臨時表僅僅存儲在物理介質中

??? 這兩種觀點都是明顯的誤區，在參考1的Q4節。表變量都是在TempDb數據庫中創建，因為表變量存儲的數據有可能超過物理內存。除此之外，我們發現只要內存足夠，表變量和臨時表都會在內存中創建和處理。它們也同樣可以在任何時間被存入磁盤。

??? 如何證明這點？請看下面代碼(在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)
    

?

??? 還有一些“證明”臨時表僅僅存在于內存中謬誤，下面我來指出其中一個：

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

??? 正確的方法應該是我前面例子中的方法，我看到很多人使用如下查詢查表變量：

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

??? 上述代碼看上去貌似很好用，但會產生多用戶的問題。你建立兩個連接，在第一個連接中創建臨時表，在第二個窗口中運行上面的語句能看到第一個連接創建的臨時表，如果你在第二個連接中嘗試操作這個臨時表，那么可能會產生錯誤，因為這個臨時表不屬于你的會話。

?

誤區3.表變量不能擁有索引。

??? 這個誤區也同樣錯誤。雖然一旦你創建一個表變量之后，就不能對其進行DDL語句了，這包括Create Index語句。然而你可以在表變量定義的時候為其創建索引)比如如下語句.

    
      declare
    
     @MyTableVariable 
    
      table
    
     (RowID intPRIMARY 
    
      KEY
    
    
      CLUSTERED
    
    ) 
  

??? 這個語句將會創建一個擁有聚集索引的表變量。由于主鍵有了對應的聚集索引，所以一個系統命名的索引將會被創建在RowID列上。

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

    
      declare
    
     @temp 
    
      TABLE
    
     (

   RowID 
    
      int
    
    
      NOT
    
    
      NULL
    
    ,

   ColA 
    
      int
    
    
      NOT
    
    
      NULL
    
    ,

   ColB 
    
      char
    
    (1)
    
      UNIQUE
    
    ,

   
    
      PRIMARY
    
    
      KEY
    
    
      CLUSTERED
    
    (RowID, ColA))
  

1) SQL 并不能為表變量建立統計信息，就像其能為臨時表建立統計信息一樣。這意味著對于表變量，執行引擎認為其只有1行，這也意味著針對表變量的執行計劃并不是最優。雖然估計的執行計劃對于表變量和臨時表都為1，但是實際的執行計劃對于臨時表會根據每次存儲過程的重編譯而改變（看參考1，Q2部分）.如果臨時表不存在，在生成執行計劃的時候會產生錯誤。

?

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

?

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

?

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

?

5) 作用域:表變量僅僅在當前的批處理中有效,并且對任何在其中嵌套的存儲過程等不可見。局部臨時表只在當前會話中有效，這也包括嵌套的存儲過程。但對父存儲過程不可見。全局臨時表可以在任何會話中可見，但是會隨著創建其的會話終止而DROP，其它會話這時就不能再引用全局臨時表。

?

6) 排序規則：表變量使用當前數據庫的排序規則，臨時表使用TempDb的排序規則。如果它們不兼容，你還需要在查詢或者表定義中進行指定(參考7. Table Variables and Temporary Tables )

?

7) 你如果希望在動態SQL中使用表變量，你必須在動態SQL中定義表變量。而臨時表可以提前定義，在動態SQL中進行引用。

?

說了這么多，那么，我該如何選擇呢？

??? 微軟 推薦 使用表變量（看參考4），如果表中的行數非常小，則使用表變量。很多”網絡專家”會告訴你100是一個分界線,因為這是統計信息創建查詢計劃效率高低的開始。但是我還是希望告訴你針對你的特定需求對臨時表和表變量進行測試。很多人在自定義函數中使用表變量，如果你需要在表變量中使用主鍵和唯一索引，你會發現包含數千行的表變量也依然性能卓越。但如果你需要將表變量和其它表進行join,你會發現由于不精準的執行計劃，性能往往會非常差。

??? 為了證明這點，請看本文的附件。附件中代碼創建了表變量和臨時表.并裝入了AdventureWorks數據庫的Sales.SalesOrderDetail表。為了得到足夠的測試數據，我將這個表中的數據插入了10遍。然后以ModifiedDate 列作為條件將臨時表和表變量與原始的Sales.SalesOrderDetail表進行了Join操作，從統計信息來看IO差別顯著。從時間來看表變量做join花了50多秒，而臨時表僅僅花了8秒。

??? 如果你需要在表建立后對表進行DLL操作，那么選擇臨時表吧。

??? 臨時表和表變量有很多類似的地方。所以有時候并沒有具體的細則規定如何選擇哪一個。對任何特定的情況，你都需要考慮其各自優缺點并做一些性能測試。下面的表格會讓你比較其優略有了更詳細的參考。

?

總結

?

參考:

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

【譯】表變量和臨時表的比較