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 @[email protected] --錯誤

·另外，由于表變量作用域有限，并且不是持久資料庫的一部分，因而不受事務復原的影響。