暫存資料表和表變數區別，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這個資料庫檔案中

本文來自博商網（wwwBS.NET）：http://wwwbs.net/blog/articleview.aspx?user=admin&artid=11633