sql server 表變數和暫存資料表

時表與永久表相似，但暫存資料表儲存在 tempdb 中，當不再使用時會自動刪除。

暫存資料表有兩種類型：本地和全域。它們在名稱、可見度以及可用性上有區別。本地暫存資料表的名稱以單個數字記號 (#) 打頭；它們僅對當前的使用者串連是可見的；當使用者從 SQL Server 執行個體中斷連線時被刪除。全域暫存資料表的名稱以兩個數字記號 (##) 打頭，建立後對任何使用者都是可見的，當所有引用該表的使用者從 SQL Server 中斷連線時被刪除。

例如，如果建立了 employees 表，則任何在資料庫中有使用該表的安全許可權的使用者都可以使用該表，除非已將其刪除。如果資料庫會話建立了本地暫存資料表 #employees，則僅會話可以使用該表，會話中斷連線後就將該表刪除。如果建立了 ##employees 全域暫存資料表，則資料庫中的任何使用者均可使用該表。如果該表在您建立後沒有其他使用者使用，則當您中斷連線時該表刪除。如果您建立該表後另一個使用者在使用該 表，則 SQL Server 將在您中斷連線並且所有其他會話不再使用該表時將其刪除。

暫存資料表

暫存資料表儲存在TempDB資料庫中,所有的使用此SQL Server 執行個體的使用者都共用這個TempDB,因為我們應該確保用來儲存TempDB資料庫的硬碟有足夠的空間,以使之能夠自己的增長.最好能夠儲存在一個擁有獨立 硬碟控制器上.因為這樣不存在和其它的硬碟I/O進行爭用.  

我們很多程式員認為暫存資料表非常危險,因為暫存資料表有可能被多個串連所共用.其實在SQL Server中存在兩種暫存資料表:局部暫存資料表和全域暫存資料表,局部暫存資料表(Local temp table)以#首碼來標識,並且只能被建立它的串連所使用.全域暫存資料表(Global temp table)以##首碼來進行標識,並且可以和其它串連所共用.

局部暫存資料表

局部暫存資料表不能夠被其它串連所共用的原因其實是在SQL Server 2000中自動為局部暫存資料表的表名後面加上了一個唯一字元來標識.如:

      CREATE TABLE [#DimCustomer_test]

      (

         [CustomerKey] [int]

         ,    [FirstName] [nvarchar](50)  

     ,[MiddleName] [nvarchar](50)  

     ,[LastName] [nvarchar](50)

         )

現在我們來查看一下TempDB中 sysobjects表,我們會發現我們新建立的暫存資料表#DimCustomer_test已經被加上了尾碼:

  USE TempDB

   GO

   SELECT name FROM sysobjects WHERE name LIKE ’%DimCustomer%’

the Result is:

name

#DimCustomer_test___________________________________________________________________________________________________000000000005

全域暫存資料表

下面我們來看一下全域暫存資料表:

      CREATE TABLE [##DimCustomer_test]

      (

         [CustomerKey] [int]

         ,       [FirstName] [nvarchar](50)  

     ,[MiddleName] [nvarchar](50)  

     ,[LastName] [nvarchar](50)

         )

現在我們來查看一下TempDB中 sysobjects表,我們會發現我們新建立的暫存資料表##DimCustomer_test沒有被加上了尾碼:

  USE TempDB

   GO

   SELECT name FROM sysobjects WHERE name LIKE ’%DimCustomer%’

The Result are:

#DimCustomer_test___________________________________________________________________________________________________000000000005

##DimCustomer_test

--Drop test temp tables

                               DROP TABLE [##DimCustomer_test]

                               DROP TABLE [#DimCustomer_test]

可以看到我們剛才建立的全域暫存資料表名字並沒有被加上標識.

表變數

表變數和暫存資料表針對我們使用人員來說並沒有什麼不同,但是在儲存方面來說,他們是不同的,表變數儲存在記憶體中.所以在效能上和暫存資料表相比會更好些!

另一個不同的地方是在表串連中使用表變數時,要為此表變數指定別名.如:

  USE AdventureWorksDW

   GO

   DECLARE @DimCustomer_test TABLE

   (

      [CustomerKey] [int]

      ,       [FirstName] [nvarchar](50)  

,[MiddleName] [nvarchar](50)  

,[LastName] [nvarchar](50)

      )

   ---insert data to @DimCustomer_test

   INSERT @DimCustomer_test

   (

      [CustomerKey]  

      ,       [FirstName]  

,[MiddleName]  

,[LastName]

      )

   SELECT  

      [CustomerKey]  

      ,       [FirstName]  

,[MiddleName]  

,[LastName]

   FROM DimCustomer

   SELECT [@DimCustomer_test].CustomerKey,SUM(FactInternetSales.OrderQuantity)

FROM @DimCustomer_test   INNER JOIN FactInternetSales    ON

@DimCustomer_test.CustomerKey = FactInternetSales.CustomerKey

Group BY CustomerKey

Result:

Server: Msg 137, Level 15, State 2, Line 32

Must declare the variable ’@DimCustomer_test’.

如果我們對上面的查詢變更,對查詢使用別名(並且找開IO):

-----in the follow script,we used the table alias.

DECLARE @DimCustomer_test TABLE

(

     [CustomerKey] [int]

     ,       [FirstName] [nvarchar](50)  

,[MiddleName] [nvarchar](50)  

,[LastName] [nvarchar](50)

     )

INSERT @DimCustomer_test

(

     [CustomerKey]  

     ,       [FirstName]  

,[MiddleName]  

,[LastName]

     )

SELECT  

     [CustomerKey]  

     ,       [FirstName]  

,[MiddleName]  

,[LastName]

FROM DimCustomer

SELECT t.CustomerKey,f.OrderQuantity

FROM @DimCustomer_test t INNER JOIN FactInternetSales   f ON

t.CustomerKey = f.CustomerKey

where t.CustomerKey=13513

表變數在批處理結束時自動被系統刪除,所以你不必要像使用暫存資料表表一樣顯示的對它進行刪除.
表變數主要開銷系統的記憶體，而暫存資料表則使用tempdb。對於小資料量的中間資料存放區，可以使用表變數，而當需要臨時儲存的資料量很龐大時，建議使用暫存資料表。具體使用表變數還是暫存資料表，可以根據系統的健全狀態來調整。

====================================================

例如，如果建立名為   employees  
的表，則任何人只要在資料庫中有使用該表的安全許可權就可以使用該表，除非它已刪除。如果建立名為   #employees  
的本地暫存資料表，只有您能對該表執行操作且在中斷連線時該表刪除。如果建立名為   ##employees  
的全域暫存資料表，資料表中的任何使用者均可對該表執行操作。如果該表在您建立後沒有其他使用者使用，則當您中斷連線時該表刪除。如果該表在您建立後有其他使用者使
用，則   SQL   Server在所有使用者中斷連線後刪除該表。

=====================================================

非索引檢視表只是一個定義, 不儲存資料, 查詢的時候才從基礎資料表拿資料

索引檢視表會儲存資料

索引檢視表和暫存資料表的資料都儲存在硬碟

其中索引檢視表的資料存放區在視圖所在的資料庫檔案中

暫存資料表的資料存放區在tempdb這個資料庫檔案中

問題 1：為什麼在已經有了暫存資料表的情況下還要引入表變數？

解答 1：與暫存資料表相比，表變數具有下列優點： • 如 SQL Server 聯機叢書“表”(Table) 一文中所述，表變數（如局部變數）具有明確定義的範圍，在該範圍結束時會自動清除這些表變數。
• 與暫存資料表相比，表變數導致預存程序的重新編譯更少。
• 涉及表變數的事務僅維持表變數上更新的期間。因此，使用表變數時，需要鎖定和記錄資源的情況更少。因為表變數具有有限的範圍並且不是持久性資料庫的一部分，所以交易回復並不影響它們。
問題 2：如果說使用表變數比使用暫存資料表導致預存程序的重新編譯更少，這意味著什嗎？

解答 2：下面的文章討論了重新編譯預存程序的一些原因：

243586 (http://support.microsoft.com/kb/243586/) 預存程序重新編譯的疑難解答
“由於某些暫存資料表操作引起的重新編譯”一節還列出了為避免一些問題（例如使用暫存資料表導致重新編譯）而需要滿足的一些要求。這些限制不適用於表變數。

表變數完全獨立於建立這些表變數的批，因此，當執行 CREATE 或 ALTER
語句時，不會發生“重新解析”，而在使用暫存資料表時可能會發生“重新解析”。暫存資料表需要此“重新解析”，以便從嵌套預存程序引用該表。表變數完全避免了此問
題，因此預存程序可以使用已編譯的計劃，從而節省了處理預存程序的資源。

問題 3：表變數有哪些缺陷？

解答 3：與暫存資料表相比，它存在下列缺陷： • 在表變數上不能建立非叢集索引（為 PRIMARY 或 UNIQUE 約束建立的系統索引除外）。與具有非叢集索引的暫存資料表相比，這可能會影響查詢效能。
• 表變數不像暫存資料表那樣可以維護統計資訊。在表變數上，不能通過自動建立或使用 CREATE STATISTICS 語句來建立統計資訊。因此，在大表上進行複雜查詢時，缺少統計資訊可能會妨礙最佳化器確定查詢的最佳計劃，從而影響該查詢的效能。
• 在初始 DECLARE 語句後不能更改表定義。
• 表變數不能在 INSERT EXEC 或 SELECT INTO 語句中使用。
• 表型別宣告中的檢查約束、預設值以及計算所得的列不能調用使用者定義的函數。
•
如果表變數是在 EXEC 語句或 sp_executesql 預存程序外建立的，則不能使用 EXEC 語句或 sp_executesql
預存程序來運行引用該表變數的動態 SQL Server 查詢。由於表變數只能在它們的本地範圍中引用，因此 EXEC 語句和
sp_executesql 預存程序將在表變數的範圍之外。但是，您可以在 EXEC 語句或 sp_executesql
預存程序內建立表變數並執行所有處理，因為這樣表變數本地範圍將位於 EXEC 語句或 sp_executesql 預存程序中。
問題 4：與暫存資料表或永久表相比，表變數的僅存在於記憶體中的結構保證了更好的效能，是否因為它們是在駐留在物理磁碟上的資料庫中維護的？

解答 4：表變數不是僅存在於記憶體中的結構。由於表變數可能保留的資料較多，記憶體中容納不下，因此它必須在磁碟上有一個位置來儲存資料。與暫存資料表類似，表變數是在 tempdb 資料庫中建立的。如果有足夠的記憶體，則表變數和暫存資料表都在記憶體（資料緩衝）中建立和處理。

問題 5：必須使用表變數來代替暫存資料表嗎？

解答 5：答案取決於以下三個因素： • 插入到表中的行數。
• 從中儲存查詢的重新編譯的次數。
• 查詢類型及其對效能的指數和統計資訊的依賴性。
在某些情況下，可將一個具有暫存資料表的預存程序拆分為多個較小的預存程序，以便在較小的單元上進行重新編譯。

通常情況下，應盡量使用表變數，除非資料量非常大並且需要重複使用表。在這種情況下，可以在暫存資料表上建立索引以提高查詢效能。但是，各種方案可能互不相同。Microsoft 建議您做一個測試，來驗證表變數對於特定的查詢或預存程序是否比暫存資料表更有效。