如果本地臨時(shí)表由存儲過程創(chuàng)建或由多個(gè)用戶同時(shí)執(zhí)行的應(yīng)用程序創(chuàng)建（其實(shí)可看作是不同的鏈接，不同的會(huì)話），則數(shù)據(jù)庫引擎必須能夠區(qū)分由不同用戶創(chuàng)建的表。為此，數(shù)據(jù)庫引擎在內(nèi)部為每個(gè)本地臨時(shí)表的表名追加一個(gè)數(shù)字后綴。存儲在 tempdb 的 sysobjects 表中的臨時(shí)表，其全名由 CREATE TABLE 語句中指定的表名和系統(tǒng)生成的數(shù)字后綴組成。為了允許追加后綴，為本地臨時(shí)表指定的 table_name 不能超過 116 個(gè)字符。

對于本地臨時(shí)表來說，需要注意在不同情形下應(yīng)用本地臨時(shí)表其刪除的實(shí)際。如假設(shè)數(shù)據(jù)庫在執(zhí)行一個(gè)存儲過程的時(shí)候建立了本地臨時(shí)表。那么此時(shí)這個(gè)本地臨時(shí)表并不是在會(huì)話終止的時(shí)候自動(dòng)刪除，而是在這個(gè)存儲過程執(zhí)行完畢后就會(huì)刪除。這是什意思呢?也就是說，用戶發(fā)起的某個(gè)會(huì)話，為了執(zhí)行一個(gè)特殊的作業(yè)(如用戶的這個(gè)會(huì)話調(diào)用了某個(gè)存儲過程)。此時(shí)其實(shí)就是會(huì)話再創(chuàng)建一個(gè)子會(huì)話的過程。在這種情況下需要注意的是，子會(huì)話創(chuàng)建的本地臨時(shí)表只在子會(huì)話內(nèi)部有效。當(dāng)這個(gè)子會(huì)話終止的時(shí)候(存儲過程執(zhí)行完畢)，此時(shí)這個(gè)臨時(shí)表就會(huì)自動(dòng)刪除。即對于調(diào)用這個(gè)子會(huì)話的會(huì)話來說，這個(gè)其子會(huì)話的創(chuàng)建的臨時(shí)表對于其也是無效的，因?yàn)榕R時(shí)表已經(jīng)在子會(huì)話關(guān)閉的時(shí)候自動(dòng)刪除。做一個(gè)形象的比喻。即現(xiàn)在做父親的去叫兒子造一座房子。當(dāng)兒子死亡的時(shí)候，這座房子也會(huì)消失。對于這種情況，數(shù)據(jù)庫管理員需要注意。父會(huì)話只能夠引用子會(huì)話從臨時(shí)表中傳遞出來的數(shù)據(jù)。也就是說，父會(huì)話要訪問子會(huì)話創(chuàng)建的臨時(shí)表的數(shù)據(jù)，只有一種手段。即先讓子會(huì)話對臨時(shí)表中的數(shù)據(jù)進(jìn)行查詢或者操作，然后把結(jié)構(gòu)回傳給父會(huì)話。父會(huì)話是不能夠直接訪問子會(huì)話所創(chuàng)建的臨時(shí)表。當(dāng)然這個(gè)限制是專門針對本地臨時(shí)表而言的。對于全局臨時(shí)表來說，本身就是所有用戶都可以訪問，為此就沒有這個(gè)限制。

全局臨時(shí)表
以兩個(gè)井號 (##) 開頭的那些表名。在所有連接上都能看到全局臨時(shí)表或者這樣說只要這個(gè)全局臨時(shí)表存在，那么用戶創(chuàng)建會(huì)話后對所有的用戶都是可見的。如果在創(chuàng)建全局臨時(shí)表的連接斷開前沒有顯式地除去這些表，那么只要所有其它任務(wù)停止引用它們，這些表即被除去。當(dāng)創(chuàng)建全局臨時(shí)表的連接斷開后，新的任務(wù)不能再引用它們（換句話說舊的任務(wù)還何以引用）。當(dāng)前的語句一執(zhí)行完，任務(wù)與表之間的關(guān)聯(lián)即被除去；因此通常情況下，只要?jiǎng)?chuàng)建全局臨時(shí)表的連接斷開，全局臨時(shí)表即被除去。

Code
--失敗的----
declare @sql varchar(100)
set @sql='select id as a,name as b into #b from shopName'
print @sql
exec(@sql)
select * from #b
drop table #b

消息 208，級別 16，狀態(tài) 0，第 5 行
對象名  '#b' 無效。


---成功的-----
declare @sql varchar(100)
set @sql='select id as a,name as b into ##b from shopName'
print @sql
exec(@sql)
select * from ##b
drop table ##b  //一定要DROP不然又要報(bào)錯(cuò)

 當(dāng)然勿論是全局的還是本地表，只要是能訪問的都能用DROP TABLE 來強(qiáng)制地刪除臨時(shí)表。

當(dāng)創(chuàng)建本地或全局臨時(shí)表時(shí)，CREATE TABLE 語法支持除 FOREIGN KEY 約束以外的其他所有約束定義。如果臨時(shí)表中指定了 FOREIGN KEY 約束，則該語句將返回一條表明已跳過此約束的警告消息。此表仍將創(chuàng)建，但不使用 FOREIGN KEY 約束。在 FOREIGN KEY 約束中不能引用臨時(shí)表。

以一個(gè)實(shí)際的例子來談?wù)勂胀ū?、本地臨時(shí)表、全局臨時(shí)表三個(gè)表的差異。如現(xiàn)在有一個(gè)保存員工信息的表user。這個(gè)表是一個(gè)普通表，只要其建立就不會(huì)自動(dòng)刪除，任何好在數(shù)據(jù)庫中有使用這個(gè)表(具有訪問權(quán)限)的用戶都可以訪問這個(gè)表，除非這個(gè)表被所有者刪除或者更改了權(quán)限。在用戶A(具有訪問權(quán)限)訪問這個(gè)表的過程中，數(shù)據(jù)庫可能會(huì)根據(jù)需要生成一張本地臨時(shí)表#user。此時(shí)只有這個(gè)會(huì)話才可以訪問這個(gè)本地臨時(shí)表。當(dāng)這個(gè)用戶的會(huì)話中斷之后，這個(gè)本地臨時(shí)表也會(huì)被自動(dòng)刪除。不過根據(jù)需要，數(shù)據(jù)庫也可能會(huì)建立全局臨時(shí)表##user(在名字上與本地臨時(shí)表不同)。此時(shí)數(shù)據(jù)庫中的任何用戶只要連接到了數(shù)據(jù)庫就可以訪問這個(gè)全局臨時(shí)表(訪問權(quán)限上的不同)。當(dāng)這個(gè)創(chuàng)建臨時(shí)表會(huì)話的用戶中斷數(shù)據(jù)庫連接時(shí)，這個(gè)臨時(shí)表是否會(huì)刪除是一個(gè)未知數(shù)，這要看當(dāng)時(shí)的實(shí)際情況(在可用性上不同)。如果此時(shí)還有其他用戶連接在這個(gè)表上的話，那么這個(gè)全局臨時(shí)表就不會(huì)被刪除。只有在中斷連接時(shí)，沒有其他用戶在訪問這個(gè)表時(shí)，即某個(gè)用戶(不一定是創(chuàng)建這張全局臨時(shí)表的用戶)斷開連接并且所有其他的會(huì)話不再使用這個(gè)表時(shí)才會(huì)被刪除。

　　可見無論是全局臨時(shí)表還是本地臨時(shí)表，其跟普通表相比，最重要的一個(gè)差異就是其會(huì)根據(jù)需要自動(dòng)創(chuàng)建。當(dāng)不再需要時(shí)其又會(huì)自動(dòng)刪除。這也正是臨時(shí)表的魅力所在，其可以在數(shù)據(jù)處理的過程中，減少很多中間表格。

臨時(shí)表對日志與鎖的影響

　　日志文件是數(shù)據(jù)庫中很重要的一個(gè)工具。無論是SQL Server數(shù)據(jù)庫還是Oracle數(shù)據(jù)庫，都有日志這個(gè)工具。如憑借重做日志工具，數(shù)據(jù)庫管理員可以在數(shù)據(jù)庫故障的時(shí)候借此來恢復(fù)數(shù)據(jù)，將數(shù)據(jù)恢復(fù)到故障的那個(gè)點(diǎn)上。但是在使用臨時(shí)表的時(shí)候，需要注意一點(diǎn)，就是臨時(shí)表不會(huì)有日志文件。即對臨時(shí)表進(jìn)行的DML等操作不會(huì)形成日志文件。這個(gè)特性即有好處，也有壞處。好處是對于臨時(shí)表的更改不會(huì)保存到日志文件中。也就是說，如果數(shù)據(jù)庫發(fā)生了故障，則保存在臨時(shí)表中的數(shù)據(jù)是不能夠恢復(fù)的。為此數(shù)據(jù)庫管理員不得不重新執(zhí)行某些作業(yè)以重新生成臨時(shí)表中的數(shù)據(jù)。好處就是對于臨時(shí)表的DML操作速度會(huì)非常的塊。除了其他的原因?qū)е缕湫阅艿奶嵘?，在更改其?nèi)容時(shí)不會(huì)生成日志信息也是一個(gè)重要的原因。為此對臨時(shí)表的操作不生成日志信息，這是一個(gè)雙刃劍。數(shù)據(jù)庫管理員在日常工作中，要盡量發(fā)揮其優(yōu)勢，減少其負(fù)面作用的影響。

　　另外，若采用臨時(shí)表這種處理機(jī)制的話，還需要注意其對鎖的影響。在介紹本地臨時(shí)表與全局臨時(shí)表差異的時(shí)候，筆者就介紹過，本地臨時(shí)表只對當(dāng)前的會(huì)話有效。即使當(dāng)前會(huì)話又創(chuàng)建了另外一個(gè)子會(huì)話，也只對子會(huì)話有效。當(dāng)某個(gè)會(huì)話終止的時(shí)候，這臨時(shí)表就會(huì)自動(dòng)被刪除。而對于普通表或者全局臨時(shí)表來說，可能同時(shí)多個(gè)會(huì)話都可以訪問這個(gè)表。這兩者有什么區(qū)別呢?若允許多個(gè)會(huì)話可以同時(shí)訪問某個(gè)表的話，那么這個(gè)表就可能會(huì)遇到鎖的情況。即某個(gè)用戶會(huì)話在對表中地記錄進(jìn)行DML等操作時(shí)，為了保證數(shù)據(jù)的一致性，會(huì)對相關(guān)的記錄進(jìn)行加鎖等措施。而采用本地臨時(shí)表的話，由于只有一個(gè)會(huì)話可以訪問臨時(shí)表中的數(shù)據(jù)，所以即使這個(gè)會(huì)話更改臨時(shí)表中的數(shù)據(jù)，也不會(huì)有鎖沖突的問題。故其在更改本地臨時(shí)表中的數(shù)據(jù)時(shí)，就不用為其加鎖。所以，對于本地臨時(shí)表的操作速度就要比其他表來的快。故在何時(shí)的情況下使用臨時(shí)表無疑可以提高數(shù)據(jù)庫的整體性能。如可以將一些操作在臨時(shí)表中完成，然后再將最后的結(jié)果更新到基本表中。

利用SQL的全局臨時(shí)表防止用戶重復(fù)登錄

      在我們開發(fā)商務(wù)軟件的時(shí)候，常常會(huì)遇到這樣的一個(gè)問題：怎樣防止用戶重復(fù)登錄我們的系統(tǒng)？特別是對于銀行或是財(cái)務(wù)部門，更是要限制用戶以其工號身份多次登入。
　　
　　可能會(huì)有人說在用戶信息表中加一字段判斷用戶工號登錄的狀態(tài)，登錄后寫1，退出時(shí)寫0，且登錄時(shí)判斷其標(biāo)志位是否為1，如是則不讓該用戶工號登錄。但是這樣那勢必會(huì)帶來新的問題：如發(fā)生象斷電之類不可預(yù)知的現(xiàn)象，系統(tǒng)是非正常退出，無法將標(biāo)志位置為0，那么下次以該用戶工號登錄則不可登入.
　　　　

Code
create procedure gp_findtemptable 
      /* 尋找以操作員工號命名的全局臨時(shí)表　　
　　* 如無則將out參數(shù)置為0并創(chuàng)建該表,如有則將out參數(shù)置為1　　
　　* 在connection斷開連接后,全局臨時(shí)表會(huì)被SQL Server自動(dòng)回收 　　
　　* 如發(fā)生斷電之類的意外,全局臨時(shí)表雖然還存在于tempdb中,但是已經(jīng)失去活性　　
　　* 用object_id函數(shù)去判斷時(shí)會(huì)認(rèn)為其不存在.
　　*/
　　@v_userid varchar(6), -- 操作員工號
　　
　　@i_out int out -- 輸出參數(shù) 0:沒有登錄 1:已經(jīng)登錄
　　
　　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

　　
　　在這個(gè)過程中，我們看到如果以用戶工號命名的全局臨時(shí)表不存在時(shí)過程會(huì)去創(chuàng)建一張并把out參數(shù)置為0，如果已經(jīng)存在則將out參數(shù)置為1。
　　
　　這樣，我們在我們的應(yīng)用程序中調(diào)用該過程時(shí)，如果取得的out參數(shù)為1時(shí)，我們可以毫不客氣地跳出一個(gè)message告訴用戶說”對不起，此工號正被使用！”

上面還涉及到一個(gè)OBJECT_ID ()函數(shù)：

Syntax:

OBJECT_ID ( '[ database_name . [ schema_name ] . | schema_name . object_name' [ ,'object_type' ] )

一般語法：int object_id('objectname');

此方法返回?cái)?shù)據(jù)庫對象標(biāo)識號。

其中，參數(shù)objectname 表示要使用的對象，其數(shù)據(jù)類型為nchar或char（如果為char，系統(tǒng)將其轉(zhuǎn)換為nchar）

object_type:為可選參數(shù)，其數(shù)據(jù)類型為nchar或char（如果為char，系統(tǒng)將其轉(zhuǎn)換為nchar），指明架構(gòu)范圍的對象類型（object_name為字符串通過它，可以說明這個(gè)字符串究竟是說明對象，其列表見文章結(jié)尾）

ps:使用 OBJECT_ID 不能查詢非架構(gòu)范圍內(nèi)的對象（如 DDL 觸發(fā)器）。對于在 sys.objects 目錄視圖中找不到的對象，需要通過查詢適當(dāng)?shù)哪夸浺晥D來獲取該對象的標(biāo)識號。例如，若要返回 DDL 觸發(fā)器的對象標(biāo)識號，請使用 SELECT OBJECT_ID FROM sys.triggers WHERE name = 'DatabaseTriggerLog'。

返回類型為int，表示該對象在系統(tǒng)中的編號，如果找不到或發(fā)生錯(cuò)誤一律返回NULL。

例子：

A.返回?cái)?shù)據(jù)庫AdventureWorks中Production.WorkOrder表的標(biāo)識號

B.存在性檢查

下列會(huì)確認(rèn)資料表有物件的標(biāo)識碼，藉此檢查指定的資料表是否存在。如果存在就刪除。

此方法一般用來判斷數(shù)據(jù)庫中本來用沒有此對象（procedures，views，functions等）.

注意：
當(dāng)該參數(shù)對系統(tǒng)函數(shù)可選時(shí)，則系統(tǒng)采用當(dāng)前數(shù)據(jù)庫、主機(jī)、服務(wù)器用戶或數(shù)據(jù)庫用戶。內(nèi)置函數(shù)后面必須跟圓括號。
如果指定一個(gè)臨時(shí)表名，除非當(dāng)前數(shù)據(jù)庫為tempdb(廢話)，否則必須在臨時(shí)表名前面加上數(shù)據(jù)庫名，例如：
SELECT OBJECT_ID('tempdb..#mytemptable')

Object_Type列表: