SQL Server暫存資料表

轉帖一： 暫存資料表與永久表相似，但暫存資料表儲存在 tempdb 中，當不再使用時會自動刪除。 暫存資料表有兩種類型：本地和全域。它們在名稱、可見度以及可用性上有區別。本地暫存資料表的名稱以單個數字記號 (#) 打頭；它們僅對當前的使用者串連是可見的；當使用者從 SQL Server 執行個體中斷連線時被刪除。全域暫存資料表的名稱以兩個數字記號 (##) 打頭，建立後對任何使用者都是可見的，當所有引用該表的使用者從 SQL Server 中斷連線時被刪除。 例如，如果建立了 employees 表，則任何在資料庫中有使用該表的安全許可權的使用者都可以使用該表，除非已將其刪除。如果資料庫會話建立了本地暫存資料表 #employees，則僅會話可以使用該表，會話中斷連線後就將該表刪除。如果建立了 ##employees 全域暫存資料表，則資料庫中的任何使用者均可使用該表。如果該表在您建立後沒有其他使用者使用，則當您中斷連線時該表刪除。如果您建立該表後另一個使用者在使用該表，則 SQL Server 將在您中斷連線並且所有其他會話不再使用該表時將其刪除。

轉帖二：

可以建立本地和全域暫存資料表。本地暫存資料表僅在當前會話中可見；全域暫存資料表在所有會話中都可見。

本地暫存資料表的名稱前面有一個編號符 (#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 約束中不能引用暫存資料表。

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

 

轉帖3

SQL SERVER暫存資料表實用大全2008-07-22 10:47

1.SQL SERVER暫存資料表實用大全 老藍筆記整理： 引子：             臨時資料表格，我們在儲存的時候經常遇見。 　　用戶端可以實用Delphi的ClientDataSet的記憶體表，但是ClientDataSet類似TABLE，不是支援SQL語言的。 　　當然也可以實用暫存資料表。有時我們避免暫存資料表的生命週期的麻煩，更多的使用實際表臨時用用。起個什麼tempXXX類似的告訴我們他們是暫存資料表。建立釋放也沒有問題。 　　那麼暫存資料表的周期是什麼樣子的呢？有什麼特殊的用法沒有？ 1.全域暫存資料表和暫存資料表的區別：視野不同。 　　　全域暫存資料表當然就是使用##打頭的表格，普通的暫存資料表格為#打頭。它們的周期應該隨一個串連也就是Connection的誕生而生，隨著Connection的斷開而死亡。它們的區別不同的地方也就是視野不同。 　　　全域表，全部授權的Connection都可以看見。但是普通暫存資料表（局域暫存資料表）僅僅建立的Connection可以看見。特別說明的是sql server的isql.exe每個串連就是一個Connection。 　　　例如：一個應用程式app.exe有僅adoconnection串連SQL SERVER資料，那麼這個串連中的adoquery或者預存程序建立的全域表##temp1，另外的一個app.exe或者其它的appx.exe都可以使用該表格。 　　　不同的是普通暫存資料表，由app.exe中的串連connection比如adoconnection的Adoquery建立的暫存資料表#temp，在所有該串連的該程式的app.exe都可以看見並且訪問，但是appx.exe或者其它的就不能看見。            陷阱：如果一個應用程式app.exe有預存程序和TADOQUERY串連同一個ADOCONNECTION.切記使用預存程序建立的暫存資料表（非全域），就不能被這個串連下的TADOQUERY訪問。原因是：預存程序本身是伺服器端執行，應該是伺服器的CONNECTION。 2.全域表或者暫存資料表的儲存和建立。 　　　估計大家都知道，暫存資料表或者全域表都是在sql server的tempdb資料庫儲存，表我們訪問的時候使用#，但是實際在進程裡面是以在tempdb裡面的使用者表以#xxx_________00000000xxx方式儲存（是否是進程標識，不大知道，也不想知道）。可以通過isql.exe的tempdb裡面看見它們。 3。頭痛初始化表格 　　初始化表格，為什麼頭痛，畢竟使用Drop什麼的。或者判斷存在比較麻煩。其實也很簡單。另外建立暫存資料表，都是無聊的SQL 陳述式，怎麼處理。 方法一：當然是使用預存程序了。可以直接寫SQL語句或者寫exec (@v_sql)。 方法二：把暫存資料表儲存到資料裡面MEMO裡面，讀取，直接執行。 　　判斷方法一：可以採用常用的exists函數。 　　　例如：if        exists        (select        *        from        tempdb..sysobjects        where        id        =        object_id(@sTmpWareA)        and        type        =        "U")     　　判斷方法二： 　　if object_id('tempdb.dbo.##'+@v_userid) is null 應用方法一：（感謝阿滿，迷糊）     一個統計的暫存資料表格，使用TADOPROC執行預存程序，如下： CREATE PROCEDURE InitCreateCoawardTable AS BEGIN     IF not (object_id('tempdb.dbo.#Tmp_Detail') is null)      drop table tempdb.dbo.#Tmp_Detail      CREATE TABLE tempdb.dbo.#Tmp_Detail ( [id] [int] IDENTITY (1, 1) NOT NULL , [單據類別] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL , [單據編號] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL , （。。這裡多複雜都可以。。。。。省略。。。）     select * from tempdb.dbo.#Tmp_Detail END GO 在用戶端使用TADOPROC執行這個預存程序，     with sproc do     begin       try        Close;        ProcedureName:=spName;        Parameters.Refresh;        Prepared;        ExecProc;        Open;//這句不能省略要不，就出現忽略了資料庫名稱 '，將引用 tempdb 中的對象錯誤。        except          Close;          Exit;        end;        Result:=True;     end; 這樣，你的TADOPROC就可以任意使用APPEND,INSERT語句了，更好的是不需要清理，而且方法靈活。更好的有點非常有效處理並發。當然有些人還在使用建立實際表格代替它，使用會話處理該問題，你還得回收。相當麻煩。而且靈活度差多了。   以下是應用範例轉載： 利用SQL Server的全域暫存資料表防止使用者重複登入　　　 在我們開發商務軟體的時候，常常會遇到這樣的一個問題：怎樣防止使用者重複登入我們的系統？特別是對於銀行或是財務部門，更是要限制使用者以其工號身份多次登入。         可能會有人說在使用者資訊表中加一欄位判斷使用者工號登入的狀態，登入後寫1，退出時寫0，且登入時判斷其標誌位是否為1，如是則不讓該使用者工號登入。但是這樣那勢必會帶來新的問題：如發生象斷電之類不可預知的現象，系統是非正常退出，無法將標誌位置為0，那麼下次以該使用者工號登入則不可登入，這該怎麼辦呢？         或許我們可以換一下思路：有什麼東西是在connection斷開後可以被系統自動回收的呢？對了，SQL Server的暫存資料表具備這個特性！但是我們這裡的這種情況不能用局部暫存資料表，因為局部暫存資料表對於每一個connection來說都是一個獨立的對象，因此只能用全域暫存資料表來達到我們的目的。         好了，情況已經明朗話了，我們可以寫一個象下面這樣簡單的預存程序:   create procedure gp_findtemptable -- 2001/10/26 21:36 zhuzhichao in nanjing /* 尋找以操作員工號命名的全域暫存資料表 * 如無則將out參數置為0並建立該表,如有則將out參數置為1 * 在connection中斷連線後,全域暫存資料表會被SQL Server自動回收 * 如發生斷電之類的意外,全域暫存資料表雖然還存在於tempdb中,但是已經失去活性 * 用object_id函數去判斷時會認為其不存在. */ @v_userid varchar(6), -- 操作員工號 @i_out int out -- 輸出參數 0:沒有登入 1:已經登入 as declare @v_sql varchar(100) if object_id('tempdb.dbo.##'+@v_userid) is null begin         set @v_sql = 'create table ##'+@v_userid+'(userid varchar(6))'         exec (@v_sql)         set @i_out = 0 end else         set @i_out = 1         在這個過程中，我們看到如果以使用者工號命名的全域暫存資料表不存在時過程會去建立一張並把out參數置為0，如果已經存在則將out參數置為1。         這樣，我們在我們的應用程式中調用該過程時，如果取得的out參數為1時，我們可以毫不客氣地跳出一個message告訴使用者說”對不起，此工號正被使用！” 判斷方法範例： select        @sTmpWareA="tempdb..[##MARWareA"+        @ComputerName+"]"       if        exists        (select        *        from        tempdb..sysobjects        where        id        =        object_id(@sTmpWareA)        and        type        =        "U")                 begin                     set        @sTmpWareA="[##MARWareA"+        @ComputerName+"]"                         exec(        "drop        table        "        +@sTmpWareA        )                 end   else                     set        @sTmpWareA="[##MARWareA"+        @ComputerName+"]"