SQL Server數(shù)據(jù)庫的存儲過程中定義的臨時(shí)表，真的有必要顯式刪除（drop table #tableName）嗎

在寫SQL Server存儲過程中，如果存儲過程中定義了臨時(shí)表，有些人習(xí)慣在存儲過程結(jié)束的時(shí)候一個一個顯式地刪除過程中定義的臨時(shí)表（drop table #tName），有些人又沒有這個習(xí)慣，對于不明真相的群眾或者喜歡思考的人會問，存儲過程中定義的臨時(shí)表，最后要不要主動刪除，為什么？
或者說是不是存儲過程結(jié)束的時(shí)候刪除臨時(shí)表更加規(guī)范？
不止一個人問過這個問題了，說實(shí)在話，本人之前確實(shí)不清楚，只是認(rèn)為，顯式刪掉或者不刪都行，臨時(shí)表在當(dāng)前Session斷開之后會自動釋放
那么存儲過程中定義的臨時(shí)表，在使用完之后，到底刪還是不刪？顯式刪除與不做刪除有無區(qū)別？
本文將對此問題進(jìn)行一個粗淺的分析，如有不對的地方，還望指出，謝謝。
存儲過程中臨時(shí)表的表結(jié)構(gòu)也有緩并且會被重用
那么到底需不需要顯式刪除,顯式刪除或者是不刪除有什么區(qū)別？
這中間涉及到一個臨時(shí)表緩存的知識點(diǎn)，首先看什么是臨時(shí)表的緩存。
緩存臨時(shí)表是SQL SERVER 2005以來的一個新特性，
臨時(shí)表的創(chuàng)建時(shí)需要往臨時(shí)庫系統(tǒng)表中寫入數(shù)據(jù)（元數(shù)據(jù)，臨時(shí)表的表結(jié)構(gòu)信息），跟普通的增刪改操作一樣，這個過程需要一定的資源消耗
在滿足一定條件的情況下（后面說需要滿足的條件是什么），
每當(dāng)用戶請求完成之后（當(dāng)然這個用戶請求的SQL中包含了臨時(shí)表），臨時(shí)表的元數(shù)據(jù)將會保存在臨時(shí)庫（tempdb）的系統(tǒng)表中
雖然在用戶看來，當(dāng)前Session創(chuàng)建的臨時(shí)表，對其他Session事不可見的，在Session斷開或者臨時(shí)表被刪除（drop）之后，將不可訪問。
但是當(dāng)新的Session調(diào)用同樣的包含了創(chuàng)建臨時(shí)表的代碼，SQL Server內(nèi)部會重用之前Session執(zhí)行時(shí)創(chuàng)建過的臨時(shí)表，而無需再次定義臨時(shí)表。
這樣的話可以節(jié)約一些創(chuàng)建表的步驟所消耗的資源。
上面是理論，下面來做個小實(shí)驗(yàn)演示上面的理論，首先來看不同Session之間臨時(shí)表“重用”的現(xiàn)象。
首先這里要借助系統(tǒng)視圖sys.dm_os_performance_counters 來判斷臨時(shí)表的創(chuàng)建次數(shù)，該系統(tǒng)表中計(jì)數(shù)器的名稱為：Temp Tables Creation Rate。
創(chuàng)建如下存儲過程，存儲過程中定義了一個臨時(shí)表，
create procedure Proc_TestTempTableasbegin
    
    create table #t20170413
    (
        col_1 varchar(100) ,
        col_2 varchar(100)
    )    insert into #t20170413 values ('aaa','bbb');    select * from #t20170413    --select * from tempdb.sys.tables where name like '#t20170413%'end
在存儲過程創(chuàng)建之后，第一次執(zhí)行的時(shí)候，來觀察一個現(xiàn)象，如下截圖

很明顯，sys.dm_os_performance_counters系統(tǒng)表中的Temp Tables Creation Rate計(jì)數(shù)器加了1，也就是說在執(zhí)行存儲過程中過程中發(fā)生了一次臨時(shí)表的創(chuàng)建動作
然后繼續(xù)再次執(zhí)行上面的代碼

同樣的代碼，這一次sys.dm_os_performance_counters系統(tǒng)表中的Temp Tables Creation Rate計(jì)數(shù)器沒有加1，
為什么明明是存儲過程中定義了臨時(shí)表，上面執(zhí)行一次，Temp Tables Creation Rate加1，然后再次執(zhí)行就不加1了?
這個就是臨時(shí)表重用的現(xiàn)象（嚴(yán)格說是臨時(shí)表的表結(jié)構(gòu)或者表定義，而不包含數(shù)據(jù)），
因?yàn)榈谝淮螆?zhí)行存儲過程的時(shí)候創(chuàng)建了臨時(shí)表，然后再次執(zhí)行存儲過程的時(shí)候就重用了第一次的臨時(shí)表?！　?br /> 　　那怎么證明該存儲過程第二次執(zhí)行的時(shí)候重用了第一次創(chuàng)建的臨時(shí)表？
　　對存儲過程稍作修改，存儲過程中加一句代碼，查詢臨時(shí)庫中該臨時(shí)表信息

　　然后執(zhí)行兩次如下代碼，下面截圖是第二次執(zhí)行的結(jié)果（下面會做解釋為什么是第二次的執(zhí)行的結(jié)果），
　　在臨時(shí)表被重用的時(shí)候查詢出來當(dāng)前臨時(shí)表的信息，發(fā)現(xiàn)臨時(shí)表創(chuàng)建次數(shù)并沒有增加，也就是說臨時(shí)表被重用了

　　既然說臨時(shí)表重用了，那么臨時(shí)表一定存在于臨時(shí)庫的系統(tǒng)表中，那么如何證明這個存儲過程的臨時(shí)表在臨時(shí)庫中呢？
　　上面顯示的臨時(shí)表的Id是-1297292959，那么這里就臨時(shí)庫中查詢Id = -1297292959的表信息，發(fā)現(xiàn)果然存在這個一張表。
　　臨時(shí)庫中的這個表信息除了名字和modify_date不一樣，modify_date據(jù)觀察是臨時(shí)表被重用的時(shí)間，也就是臨時(shí)表被重用一次就修改一次modify_date
　　其他信息完全一致，這就是說明，存儲過程第一次執(zhí)行完成之后，它所創(chuàng)建的臨時(shí)表被緩存了起來（至于名字不同，后面再解釋），
　　當(dāng)再次執(zhí)行該存儲過程的時(shí)候可以重用第一次執(zhí)行存儲過程時(shí)候創(chuàng)建的臨時(shí)表的表結(jié)構(gòu)。

存儲過程中顯式刪除臨時(shí)表，到底有沒有用處？
對上面的存儲過程做如下修改，在存儲過程結(jié)束之前顯式刪除定義的臨時(shí)表

　　然后再次執(zhí)行如下的測試代碼，注意截圖是第二次執(zhí)行的結(jié)果（下面會做解釋為什么是第二次的執(zhí)行的結(jié)果）

　　然后繼續(xù)在臨時(shí)庫的系統(tǒng)表中查詢上述Id的系統(tǒng)，發(fā)現(xiàn)臨時(shí)表依舊存在于系統(tǒng)表中，即便是存儲過程中顯式刪除（drop table #t20170413）

　　這里說明，即便在存儲過程中顯式調(diào)用了刪除臨時(shí)表的操作，臨時(shí)表依舊會存在得臨時(shí)庫的系統(tǒng)表中，也就是說臨時(shí)表依舊會被緩存。
　　并不會因?yàn)樵诖鎯^程中顯式刪除而真正的刪除，臨時(shí)表對象會緩存在臨時(shí)庫的系統(tǒng)表中。
　　之所以Session中查詢到的臨時(shí)表的名字與系統(tǒng)表中查詢到的臨時(shí)表的名字不同，
　　原因是臨時(shí)表從創(chuàng)建到緩存（當(dāng)前Session斷開之后），在內(nèi)部只是發(fā)生了一個對當(dāng)前Session臨時(shí)表重命名的過程。
　　被緩存的臨時(shí)表的重用的過程與上面的類似，也是將緩存的換反向重命名。
事實(shí)證明：
對于存儲過程的臨時(shí)表，在滿足可緩存的前提下（只是表結(jié)構(gòu)，當(dāng)然不包括臨時(shí)表的數(shù)據(jù)），
你刪，或者不刪，他都會緩存在臨時(shí)庫中，并不因?yàn)轱@式Drop臨時(shí)表，臨時(shí)表就會被真正的刪除，這是SQL Server專門為此做的優(yōu)化，你真的不用為刪除臨時(shí)表而操心或者糾結(jié)
這里回到一開始的問題，存儲過程中有沒有必要顯式刪除臨時(shí)表就有答案了：對于存儲過程的創(chuàng)建的臨時(shí)表，沒必要刪除，對于滿足可緩存的臨時(shí)表對象，想刪也刪不掉！
存儲過程中定義的臨時(shí)表，只有滿足一定的條件，才會被緩存重用
　　上面說了，臨時(shí)表的重用是要滿足一定條件的，如下條件將會導(dǎo)致臨時(shí)表無法重用
1，創(chuàng)建臨時(shí)表的時(shí)候存在命名約束（這一點(diǎn)非常操蛋，不僅僅是緩存問題，曾經(jīng)遇到過坑，有機(jī)會演示）
2，在臨時(shí)表創(chuàng)建之后執(zhí)行DDL操作，比如創(chuàng)建索引等，但是這個DDL不包括drop 臨時(shí)表和truncate臨時(shí)表
3，動態(tài)SQL方式創(chuàng)建的臨時(shí)表
4，在不同的范圍之內(nèi)創(chuàng)建的臨時(shí)表，應(yīng)該是存儲過程調(diào)用另外一個存儲過程，另外一個存儲過程定義的臨時(shí)表，這一點(diǎn)還沒有具體研究
5，存儲過程以WITH RECOMPILE重編譯的方式運(yùn)行
　　比如在上面的存儲過程，在臨時(shí)表定義之后，創(chuàng)建一個索引，
　　此舉將會造成臨時(shí)表無法重用，這種情況下，不管你刪或者不刪，存儲過程執(zhí)行完成Session斷開之后，臨時(shí)表都不會緩存（在臨時(shí)庫中）
　　這一點(diǎn)就不截圖演示了，有興趣的自己測試

　　解釋另外一個問題：
　　既然認(rèn)為無法刪除緩存的臨時(shí)表，正常情況下，緩存的臨時(shí)表什么情況下會被刪除？
　　上面說截圖都是第二次運(yùn)行的截圖，因?yàn)樵诖鎯^程重建之后（create或者alter），這個存儲過程中定義的臨時(shí)表都會被清理掉
　　只有重建了存儲過程，第一次執(zhí)行之后，緩存的臨時(shí)表在第二次執(zhí)行的時(shí)候才能被重用
　　當(dāng)然這一點(diǎn)也和容易驗(yàn)證，緩存臨時(shí)表之后，然后alter 存儲過程，
　　然后根據(jù)緩存臨時(shí)表的Id去查詢臨時(shí)庫中sys .tables的信息，這個緩存的表會在1~2秒之后被刪除（個人測試驗(yàn)證過）
　　另外顯式執(zhí)行DBCC FREEPROCCACHE，也能刪除緩存的臨時(shí)表。
　　其實(shí)也不難理解，緩存的對象是跟執(zhí)行計(jì)劃緩存綁定的，如果執(zhí)行計(jì)劃本身就不存在了，那么緩存的臨時(shí)表對象也將會被請?zhí)幚怼?br /> 并發(fā)執(zhí)行的情況下，臨時(shí)表能否重用？
　　并發(fā)線程之間當(dāng)然不會重用同一個臨時(shí)表，如果不是這樣的話，SQL Server也不用混江湖了，并發(fā)的每個線程會創(chuàng)建自己的臨時(shí)表。
　　參考如下截圖是在并發(fā)情況下，tempdb產(chǎn)生的臨時(shí)表的情況，每個線程調(diào)用存儲過程產(chǎn)生的臨時(shí)表后綴都是不一樣的。
　　并發(fā)調(diào)用存儲過程的時(shí)候，每個線程會產(chǎn)生屬于自己的臨時(shí)表，重用臨時(shí)表是發(fā)生在當(dāng)前線程執(zhí)行完成之后，其他Session重新調(diào)用存儲過程時(shí)候才能重用已緩存的臨時(shí)表。
　　鑒于本文不是專門說明臨時(shí)表的，這里就不多說了。

顯式刪除臨時(shí)表與否的性能測試
既然上面說了，如果存儲過程中定義的臨時(shí)表滿足臨時(shí)表被緩存的條件的情況下，存儲過程中是否刪除臨時(shí)表，臨時(shí)表都一樣會被緩存
那么，如果真的指定了顯式刪除臨時(shí)表操作，與沒有顯式指定刪除臨時(shí)表，性能上有沒有差別呢？
抱著以數(shù)據(jù)說話的態(tài)度，分別在存儲過程中不刪除與顯式刪除臨時(shí)表，用SQLQueryStress做了一系列的性能測試
結(jié)果如下
不顯式刪除臨時(shí)表    顯式刪除臨時(shí)表
   
測試結(jié)果如下， 

　　測試過程部分截圖（不浪費(fèi)博客園的圖片服務(wù)器資源了，隨便截了兩張）

　　從測試結(jié)果看，確實(shí)有一些差異，不過這個差別是非常小的，
　　第一組測試結(jié)果5000次調(diào)用產(chǎn)生了0.07秒的差距
　　第二組測試結(jié)果20000次調(diào)用產(chǎn)生了0.35秒的差距，平均到一次差距也就在微妙級，即便是顯式調(diào)用刪除，對性能來說是有一點(diǎn)點(diǎn)影響，不過這個影響也是無傷大雅。
　　不過這個內(nèi)部的原始一定要弄清楚，有沒有必要刪除，以及原因，這個才是原則性的問題！
　   至于臨時(shí)表數(shù)據(jù)占用的空間，也不是說顯式刪除就釋放，不刪除就不釋放，應(yīng)該是有后臺進(jìn)程來做這個工作的，個人建議不用為這個問題瞎操心。
　　寫存儲過程的時(shí)候，多寫一點(diǎn)好一點(diǎn)的SQL語句，比糾結(jié)這個強(qiáng)多了。
多啰嗦一句：
有些人的觀念是根深蒂固的，對于習(xí)慣刪除臨時(shí)表的人，覺得這么做“規(guī)范”，“專業(yè)”，雖然他沒有確切的理由說明顯式刪除臨時(shí)表的必要性。
但是你要跟他說沒必要刪除臨時(shí)表，一定會激怒他，好多程序員都是這樣的，你否認(rèn)他根深蒂固的一個觀點(diǎn)的時(shí)候，他是很惱火的。
從生物學(xué)上說，這個是屬于“印隨行為”，如宗教般，在自己處于懵懂期的時(shí)候，受到一些說法的影響
或許是當(dāng)初的師傅說的，或者膜拜的對象這么做了，或者聽高人說過這么做比較好，然后自己就一直這么做了并且堅(jiān)信不疑。
當(dāng)然，包括我自己在某些時(shí)候也有此種行為，思維被曾經(jīng)的某一些經(jīng)歷固化，然后一直束縛自己的認(rèn)知。
不過對于無傷大雅的問題，就隨他去了，沒必要說服他，弄不好他反過來覺得你業(yè)余，希望小伙伴們明辨，好似乎跑題了……
顯式刪除臨時(shí)表與否與臨時(shí)庫空間釋放問題
　　有人擔(dān)心說，如果不顯式刪除臨時(shí)表，是不是臨時(shí)表占用的空間無法快速釋放？
　　其實(shí)也不用顧慮，還是以數(shù)據(jù)說話，這里對比兩個一樣的存儲過程，一個不顯式刪除臨時(shí)表，一個顯示刪除臨時(shí)表，看看臨時(shí)數(shù)據(jù)庫中用戶對象占用page的情況
　　不顯式刪除臨時(shí)表的存儲過程
　　做如下對比測試，借助SQLQueryStress，做一個20線程，每個線程500次循環(huán)的測試

　　測試的過程中，在臨時(shí)數(shù)據(jù)庫中，利用如下SQL，間隔一秒的頻率抓取臨時(shí)庫中user objects對象的數(shù)據(jù)

　　把上述腳本記錄到的數(shù)據(jù)，利用Excel的透視圖功能，呈現(xiàn)出來上述腳本記錄到的user objects數(shù)量，可以很清楚地發(fā)現(xiàn)，不顯式刪除臨時(shí)表，與顯式刪除臨時(shí)表相比，UserObjecs數(shù)量并沒有明顯的差異
　　也就數(shù)說，不顯式刪除臨時(shí)表的情況下，并沒有出現(xiàn)臨時(shí)表空間對象釋放不及時(shí)的情況

 　　因此大可不必?fù)?dān)心，不顯式刪除臨時(shí)表，臨時(shí)表申請的空間無法及時(shí)釋放。
　　總結(jié)：　　　
　　本文從存儲過程中的臨時(shí)表是否需要顯式刪除入手，簡單介紹了臨時(shí)表重用的現(xiàn)象和前提條件，以及有無必要顯式刪除臨時(shí)表，
　　同時(shí)測試了臨時(shí)表在滿足重用的情況下，臨時(shí)表顯式刪除與否的性能問題，對于存儲過程中定義的臨時(shí)表，不管是否能否緩存重用，都不建議顯式刪除。