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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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