在SQL開發(fā)中，臨時表是一種非常實(shí)用的工具，可以在不影響數(shù)據(jù)庫結(jié)構(gòu)的情況下臨時存儲數(shù)據(jù)、加快查詢速度、簡化復(fù)雜的SQL操作。本文將深入探討SQL臨時表的定義、分類和示例。

一、臨時表定義

臨時表與實(shí)體表類似，只是在使用過程中，臨時表是存儲在系統(tǒng)數(shù)據(jù)庫tempdb中。當(dāng)我們不再使用臨時表的時候，臨時表會自動刪除。

二、臨時表分類

臨時表分為本地臨時表和全局臨時表，它們在名稱、可見性以及可用性上有區(qū)別。

三、臨時表的特性

對于臨時表有如下幾個特點(diǎn)：

• 本地臨時表就是用戶在創(chuàng)建表的時候添加了"#"前綴的表，其特點(diǎn)是根據(jù)數(shù)據(jù)庫連接獨(dú)立。只有創(chuàng)建本地臨時表的數(shù)據(jù)庫連接有表的訪問權(quán)限，其它連接不能訪問該表；
• 全局臨時表是用戶在創(chuàng)建表的時候添加"##"前綴的表，其特點(diǎn)是所以數(shù)據(jù)庫連接均可使用該全局臨時表，當(dāng)所有引用該臨時表的數(shù)據(jù)庫連接斷開后自動刪除。
• 全局臨時表相比本地臨時表，命名上就需要注意了，與本地臨時表不同的是，全局臨時表名不能重復(fù)。
• 臨時表利用了數(shù)據(jù)庫臨時表空間，由數(shù)據(jù)庫系統(tǒng)自動進(jìn)行維護(hù)，因此節(jié)省了物理表空間。并且由于臨時表空間一般利用虛擬內(nèi)存，大大減少了硬盤的I/O次數(shù)，因此也提高了系統(tǒng)效率。
• 臨時表在事務(wù)完畢或會話完畢數(shù)據(jù)庫會自動清空，不必記得用完后刪除數(shù)據(jù)。

四、本地臨時表

本地臨時表的名稱以單個數(shù)字符號"#" 打頭；它們僅對當(dāng)前的用戶連接（也就是創(chuàng)建本地臨時表的connection）是可見的；當(dāng)用戶從 SQL Server 實(shí)例斷開連接時被刪除。

本地臨時表實(shí)例

我們以Customers表為實(shí)例，表數(shù)據(jù)如下：

Customers

我們新建一個連接，每當(dāng)“新建查詢”就代表打開了一個連接，連接的ID就是sa后面的數(shù)字，我們的這個連接ID是57.

下面我們在這個查詢頁面建立一個臨時表。

SELECT * INTO #Customers FROM Customers

這樣我們就建好了一個臨時表，可以查詢一下臨時表#Customers的數(shù)據(jù)。與Customers內(nèi)容一致。

SELECT * FROM #Customers

如果我們再打開一個頁面，同樣查詢#Customers表會怎么樣呢？

我們在新開的查詢頁面執(zhí)行上述查詢語句，得到的結(jié)果如下：

說明本地臨時表不支持跨連接查詢。只能在當(dāng)前連接(或者當(dāng)前查詢頁面)訪問。

那本地臨時表具體在什么地方呢？它又是怎么存放的呢？

這就是我們剛才建立的臨時表，在系統(tǒng)中并不是用#Cusomters這樣的表名來表示的。

五、全局臨時表

全局臨時表的名稱以兩個數(shù)字符號 "##"打頭，創(chuàng)建后對任何數(shù)據(jù)庫連接都是可見的，當(dāng)所有引用該表的數(shù)據(jù)庫連接從 SQL Server 斷開時被刪除。

全局臨時表實(shí)例

我們還是按照上面的步驟走一遍

先打開一個查詢頁面，輸入如下查詢語句：

SELECT * INTO ##Customers FROM Customers

執(zhí)行完上面的查詢語句后，我們關(guān)掉查詢頁面，再重新開一個頁面查詢##Customers中的內(nèi)容

SELECT * FROM ##Customers

結(jié)果如下：

此時并不會像本地臨時表那樣報錯了。

全局臨時表的位置如下：

它的名稱與我們自定義的名稱一致，系統(tǒng)不會額外添加其他信息。

六、臨時表的用途

介紹完臨時表，我們來說說如何用它來進(jìn)行優(yōu)化

臨時表的優(yōu)化一般使用在子查詢較多的情況下，也稱為嵌套查詢。我們寫如下子查詢：

SELECT * FROM sales.Temp_Salesorder
WHERE SalesOrderDetailID IN 
(SELECT SalesOrderDetailID 
FROM sales.SalesOrderDetail
WHERE UnitPrice IN
(SELECT UnitPrice 
FROM sales.SalesOrderDetail 
WHERE UnitPrice>0)
)

這是一個比較簡單的兩層嵌套子查詢，我們看一下執(zhí)行情況：

可以看到這里的邏輯讀取是比較高的。

我們用臨時表重新來看下執(zhí)行情況如何，我們將第一二層的查詢結(jié)果插入到#temp中，然后從臨時表中查詢結(jié)果。

SELECT SalesOrderDetailID INTO #temp 
FROM sales.SalesOrderDetail
WHERE UnitPrice IN 
(SELECT UnitPrice 
FROM sales.SalesOrderDetail 
WHERE UnitPrice>0)

SELECT * FROM sales.Temp_Salesorder
WHERE SalesOrderDetailID IN 
(SELECT SalesOrderDetailID FROM #temp)

執(zhí)行情況如下：

相比上一次的邏輯讀，成倍的減少了邏輯讀取次數(shù)。

在對查詢的性能進(jìn)行調(diào)節(jié)時，如果邏輯讀值下降，就表明查詢使用的服務(wù)器資源減少，查詢的性能有所提高。如果邏輯讀值增加，則表示調(diào)節(jié)措施降低了查詢的性能。

在其他條件不變的情況下，一個查詢使用的邏輯讀越少，其效率就越高，查詢的速度就越快。

因此我們可以看出臨時表在比較復(fù)雜的嵌套查詢中是可以提高查詢效率的。

在實(shí)際的SQL開發(fā)中，合理地使用臨時表可以提高查詢性能、簡化操作流程，是提升工作效率的利器。

重要聲明：本文來自SQL數(shù)據(jù)庫開發(fā)，經(jīng)授權(quán)轉(zhuǎn)載，版權(quán)歸原作者所有，不代表銳成觀點(diǎn)，轉(zhuǎn)載的目的在于傳遞更多知識和信息。