SQL SERVER暫存資料表的使用

SQL SERVER暫存資料表的使用
--刪除暫存資料表#Tmp
create table #Tmp  --建立暫存資料表#Tmp
(
    ID   int IDENTITY (1,1)     not null, --建立列ID,並且每次新增一條記錄就會加1
    WokNo                varchar(50),  
    primary key (ID)      --定義ID為暫存資料表#Tmp的主鍵     
);
Select * from #Tmp    --查詢暫存資料表的資料
truncate table #Tmp  --清空暫存資料表的所有資料和約束

相關例子：

Declare @Wokno Varchar(500)  --用來記錄職工號
Declare @Str NVarchar(4000)  --用來存放查詢語句
Declare @Count int  --求出總記錄數     
Declare @i int
Set @i = 0
Select @Count = Count(Distinct(Wokno)) from #Tmp
While @i < @Count
    Begin
       Set @Str = 'Select top 1 @Wokno = WokNo from #Tmp Where id not in (Select top ' + Str(@i) + 'id from #Tmp)'
       Exec Sp_ExecuteSql @Str,N'@WokNo Varchar(500) OutPut',@WokNo Output
       Select @WokNo,@i  --一行一行把職工號顯示出來
       Set @i = @i + 1
    End
暫存資料表
可以建立本地和全域暫存資料表。本地暫存資料表僅在當前會話中可見；全域暫存資料表在所有會話中都可見。

本地暫存資料表的名稱前面有一個編號符 (#table_name)，而全域暫存資料表的名稱前面有兩個編號符 (##table_name)。

SQL 陳述式使用 CREATE TABLE 語句中為 table_name 指定的名稱引用暫存資料表：

CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)
INSERT INTO #MyTempTable VALUES (1)

如果本地暫存資料表由預存程序建立或由多個使用者同時執行的應用程式建立，則 SQL Server 必須能夠區分由不同使用者建立的表。為此，SQL Server 在內部為每個本地暫存資料表的表名追
加一個數字尾碼。儲存在 tempdb 資料庫的 sysobjects 表中的暫存資料表，其全名由 CREATE TABLE 語句中指定的表名和系統產生的數字尾碼組成。為了允許追加尾碼，為本地暫存資料表指定的表名 table_name 不能超過 116 個字元。

除非使用 DROP TABLE 語句顯式除去暫存資料表，否則暫存資料表將在退出其範圍時由系統自動除去：

當預存程序完成時，將自動除去在預存程序中建立的本地暫存資料表。由建立表的預存程序執行的所有嵌套預存程序都可以引用此表。但調用建立此表的預存程序的進程無法引用此表
。

所有其它本地暫存資料表在當前會話結束時自動除去。

全域暫存資料表在建立此表的會話結束且其它任務停止對其引用時自動除去。任務與表之間的關聯只在單個 Transact-SQL 陳述式的生存周期內保持。換言之，當建立全域暫存資料表的會話
結束時，最後一條引用此表的 Transact-SQL 陳述式完成後，將自動除去此表。
在預存程序或觸發器中建立的本地暫存資料表與在調用預存程序或觸發器之前建立的同名暫存資料表不同。如果查詢引用暫存資料表，而同時有兩個同名的暫存資料表，則不定義針對哪個表解析該
查詢。嵌套預存程序同樣可以建立與調用它的預存程序所建立的暫存資料表同名的暫存資料表。嵌套預存程序中對錶名的所有引用都被解釋為是針對該嵌套過程所建立的表，例如：

CREATE PROCEDURE Test2
AS
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (2)
SELECT Test2Col = x FROM #t
GO
CREATE PROCEDURE Test1
AS
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (1)
SELECT Test1Col = x FROM #t
EXEC Test2
GO
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (99)
GO
EXEC Test1
GO

下面是結果集：

(1 row(s) affected)

Test1Col   
-----------
1          

(1 row(s) affected)

Test2Col   
-----------
2          

當建立本地或全域暫存資料表時，CREATE TABLE 文法支援除 FOREIGN KEY 約束以外的其它所有約束定義。如果在暫存資料表中指定 FOREIGN KEY 約束，該語句將返回警告資訊，指出此約
束已被忽略，表仍會建立，但不具有 FOREIGN KEY 約束。在 FOREIGN KEY 約束中不能引用暫存資料表。

考慮使用表變數而不使用暫存資料表。當需要在暫存資料表上顯式地建立索引時，或多個預存程序或函數需要使用表值時，暫存資料表很有用。通常，表變數提供更有效查詢處理。
 
=======================================================================================================================================================

表和表變數有什麼區別?
什麼時候用表好,什麼時候用表變數好?
 
--------------------------------
表是實體，資料儲存在資料庫檔案內。

表變數是一種特殊變數，只對當前會話有效。
 
-----------------------------------------
對於表變數，《聯機叢書》如下描述：

一種特殊的資料類型，用於儲存結果集以供後續處理。該資料類型主要用於臨時儲存一組行，這些行將作為資料表值函式的結果集返回。

儘可能使用表變數而不使用暫存資料表。table 變數有以下優點：

·table 變數的行為類似於局部變數，有明確定義的範圍。該範圍為聲明該變數的函數、預存程序或批處理。
在其範圍內，table 變數可像常規表那樣使用。該變數可應用於 SELECT、INSERT、UPDATE 和 DELETE 語句中用到表或表的運算式的地方。但是，table 不能用在下列語句中：
　　INSERT INTO table_variable EXEC 預存程序。
　　SELECT select_list INTO table_variable 語句。
在定義 table 變數的函數、預存程序或批處理結束時，自動清除 table 變數。

·表型別宣告中的 CHECK 條件約束、DEFAULT 值和計算資料行不能調用使用者定義函數。

·在預存程序中使用 table 變數與使用暫存資料表相比，減少了預存程序的重新編譯量。

·涉及表變數的事務只在表變數更新期間存在。這樣就減少了表變數對鎖定和記錄資源的需求。

·不支援在表變數之間進行賦值操作。
　　declare @t1 table(t1 int)
　　declare @t2 table(t2 int)
　　set @t1=@t2 --錯誤

·另外，由於表變數範圍有限，並且不是持久資料庫的一部分，因而不受交易回復的影響。