SQL中的臨時(shí)表
點(diǎn)擊關(guān)注上方“SQL數(shù)據(jù)庫(kù)開(kāi)發(fā)”，
設(shè)為“置頂或星標(biāo)”，第一時(shí)間送達(dá)干貨
臨時(shí)表定義
臨時(shí)表與實(shí)體表類(lèi)似，只是在使用過(guò)程中，臨時(shí)表是存儲(chǔ)在系統(tǒng)數(shù)據(jù)庫(kù)tempdb中。當(dāng)我們不再使用臨時(shí)表的時(shí)候，臨時(shí)表會(huì)自動(dòng)刪除。
臨時(shí)表分類(lèi)
臨時(shí)表分為本地臨時(shí)表和全局臨時(shí)表，它們?cè)诿Q(chēng)、可見(jiàn)性以及可用性上有區(qū)別。
臨時(shí)表的特性
對(duì)于臨時(shí)表有如下幾個(gè)特點(diǎn)：
本地臨時(shí)表就是用戶在創(chuàng)建表的時(shí)候添加了"#"前綴的表，其特點(diǎn)是根據(jù)數(shù)據(jù)庫(kù)連接獨(dú)立。只有創(chuàng)建本地臨時(shí)表的數(shù)據(jù)庫(kù)連接有表的訪問(wèn)權(quán)限，其它連接不能訪問(wèn)該表；
不同的數(shù)據(jù)庫(kù)連接中，創(chuàng)建的本地臨時(shí)表雖然"名字"相同，但是這些表之間相互并不存在任何關(guān)系；在SQLSERVER中，通過(guò)特別的命名機(jī)制保證本地臨時(shí)表在數(shù)據(jù)庫(kù)連接上的獨(dú)立性，意思是你可以在不同的連接里使用相同的本地臨時(shí)表名稱(chēng)。
全局臨時(shí)表是用戶在創(chuàng)建表的時(shí)候添加"##"前綴的表，其特點(diǎn)是所以數(shù)據(jù)庫(kù)連接均可使用該全局臨時(shí)表，當(dāng)所有引用該臨時(shí)表的數(shù)據(jù)庫(kù)連接斷開(kāi)后自動(dòng)刪除。
全局臨時(shí)表相比本地臨時(shí)表，命名上就需要注意了，與本地臨時(shí)表不同的是，全局臨時(shí)表名不能重復(fù)。
臨時(shí)表利用了數(shù)據(jù)庫(kù)臨時(shí)表空間，由數(shù)據(jù)庫(kù)系統(tǒng)自動(dòng)進(jìn)行維護(hù)，因此節(jié)省了物理表空間。并且由于臨時(shí)表空間一般利用虛擬內(nèi)存，大大減少了硬盤(pán)的I/O次數(shù)，因此也提高了系統(tǒng)效率。
臨時(shí)表在事務(wù)完畢或會(huì)話完畢數(shù)據(jù)庫(kù)會(huì)自動(dòng)清空，不必記得用完后刪除數(shù)據(jù)。
本地臨時(shí)表
本地臨時(shí)表的名稱(chēng)以單個(gè)數(shù)字符號(hào)"#" 打頭；它們僅對(duì)當(dāng)前的用戶連接（也就是創(chuàng)建本地臨時(shí)表的connection）是可見(jiàn)的；當(dāng)用戶從 SQL Server 實(shí)例斷開(kāi)連接時(shí)被刪除。
本地臨時(shí)表實(shí)例
我們以Customers表為實(shí)例，表數(shù)據(jù)如下：
我們新建一個(gè)連接，每當(dāng)“新建查詢(xún)”就代表打開(kāi)了一個(gè)連接，連接的ID就是sa后面的數(shù)字，我們的這個(gè)連接ID是57.
下面我們?cè)谶@個(gè)查詢(xún)頁(yè)面建立一個(gè)臨時(shí)表。
SELECT * INTO #Customers FROM Customers這樣我們就建好了一個(gè)臨時(shí)表，可以查詢(xún)一下臨時(shí)表#Customers的數(shù)據(jù)。與Customers內(nèi)容一致。
SELECT * FROM #Customers
如果我們?cè)俅蜷_(kāi)一個(gè)頁(yè)面，同樣查詢(xún)#Customers表會(huì)怎么樣呢？
我們?cè)谛麻_(kāi)的查詢(xún)頁(yè)面執(zhí)行上述查詢(xún)語(yǔ)句，得到的結(jié)果如下：
說(shuō)明本地臨時(shí)表不支持跨連接查詢(xún)。只能在當(dāng)前連接(或者當(dāng)前查詢(xún)頁(yè)面)訪問(wèn)。
那本地臨時(shí)表具體在什么地方呢？它又是怎么存放的呢？
這就是我們剛才建立的臨時(shí)表，在系統(tǒng)中并不是用#Cusomters表示的。
全局臨時(shí)表
全局臨時(shí)表的名稱(chēng)以?xún)蓚€(gè)數(shù)字符號(hào) "##"打頭，創(chuàng)建后對(duì)任何數(shù)據(jù)庫(kù)連接都是可見(jiàn)的，當(dāng)所有引用該表的數(shù)據(jù)庫(kù)連接從 SQL Server 斷開(kāi)時(shí)被刪除。
全局臨時(shí)表實(shí)例
我們還是按照上面的步驟走一遍
先打開(kāi)一個(gè)查詢(xún)頁(yè)面，輸入如下查詢(xún)語(yǔ)句：
SELECT * INTO ##Customers FROM Customers執(zhí)行完上面的查詢(xún)語(yǔ)句后，我們關(guān)掉查詢(xún)頁(yè)面，再重新開(kāi)一個(gè)頁(yè)面查詢(xún)##Customers中的內(nèi)容
SELECT * FROM ##Customers結(jié)果如下：
此時(shí)并不會(huì)像本地臨時(shí)表那樣報(bào)錯(cuò)了。
全局臨時(shí)表的位置如下：
它的名稱(chēng)與我們自定義的名稱(chēng)一致，系統(tǒng)不會(huì)額外添加其他信息。
臨時(shí)表的用途
介紹完臨時(shí)表，我們來(lái)說(shuō)說(shuō)如何用它來(lái)進(jìn)行優(yōu)化
臨時(shí)表的優(yōu)化一般使用在子查詢(xún)較多的情況下，也稱(chēng)為嵌套查詢(xún)。我們寫(xiě)如下子查詢(xún)：
SELECT * FROM sales.Temp_Salesorder
WHERE SalesOrderDetailID IN
(SELECT SalesOrderDetailID FROM sales.SalesOrderDetail
WHERE UnitPrice IN
(SELECT UnitPrice FROM sales.SalesOrderDetail WHERE UnitPrice>0)
)
(提示：代碼可以左右滑動(dòng))
這是一個(gè)比較簡(jiǎn)單的兩層嵌套子查詢(xún)，我們看一下執(zhí)行情況：
可以看到這里的邏輯讀取是比較高的。
我們用臨時(shí)表重新來(lái)看下執(zhí)行情況如何，我們將第一二層的查詢(xún)結(jié)果插入到#temp中，然后從臨時(shí)表中查詢(xún)結(jié)果。
注：上圖的統(tǒng)計(jì)信息需開(kāi)啟SSMS相關(guān)功能，具體如下：
工具—選項(xiàng)—查詢(xún)執(zhí)行—高級(jí)
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ù)。在對(duì)查詢(xún)的性能進(jìn)行調(diào)節(jié)時(shí)，如果邏輯讀值下降，就表明查詢(xún)使用的服務(wù)器資源減少，查詢(xún)的性能有所提高。如果邏輯讀值增加，則表示調(diào)節(jié)措施降低了查詢(xún)的性能。在其他條件不變的情況下，一個(gè)查詢(xún)使用的邏輯讀越少，其效率就越高，查詢(xún)的速度就越快。
因此我們可以看出臨時(shí)表在比較復(fù)雜的嵌套查詢(xún)中是可以提高查詢(xún)效率的。
總結(jié)
臨時(shí)表不管是在SQL Server還是其他平臺(tái)都有使用，其在查詢(xún)優(yōu)化方面可以極大的提高查詢(xún)效率，而SQL Server平臺(tái)的臨時(shí)表相比其他平臺(tái)更容易創(chuàng)建和使用，其優(yōu)越性不言而喻。所以如果平時(shí)工作或?qū)W習(xí)過(guò)程中，臨時(shí)表可以作為一個(gè)必備技能經(jīng)常使用。
最后給大家分享我寫(xiě)的SQL兩件套：《SQL基礎(chǔ)知識(shí)第二版》和《SQL高級(jí)知識(shí)第二版》的PDF電子版。里面有各個(gè)語(yǔ)法的解釋、大量的實(shí)例講解和批注等等，非常通俗易懂，方便大家跟著一起來(lái)實(shí)操。后臺(tái)回復(fù)關(guān)鍵字：1024，獲取一份精心整理的技術(shù)干貨
后臺(tái)回復(fù)關(guān)鍵字：進(jìn)群，帶你進(jìn)入高手如云的交流群。