這個案例是前幾天同事遇到的一個案例，在存儲過程中“刪除”了一個臨時表，然後重新創建這個臨時表時遇到“There is already an object named 'xxxx' in the database."這樣的錯誤。下麵簡單演示一下這個案例（不用存儲過程，而是直接用簡單的SQL語句重現） ... ...
這個案例是前幾天同事遇到的一個案例，在存儲過程中“刪除”了一個臨時表，然後重新創建這個臨時表時遇到“There is already an object named 'xxxx' in the database."這樣的錯誤。下麵簡單演示一下這個案例（不用存儲過程，而是直接用簡單的SQL語句重現）
CREATE TABLE #temp_test( id INT, name VARCHAR(32));
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE name='#tmp_test')
BEGINDROP TABLE #temp_test;
ENDGOCREATE TABLE #temp_test( id INT, name VARCHAR(32));
同事很是不解，問我為什麼在存儲過程裡面“刪除”了這個臨時表居然不能重建呢？ 其實這裡面並沒有什麼玄機，而是僅僅犯了邏輯錯誤。上面這個SQL語句，其實永遠也不會刪除這個臨時表。
原因很簡單，臨時表#temp_test在tempdb..sysobjects裡面存儲的名字為“#temp_test_________________________________________________________________0000000000EE”,所以上述腳本犯了一個邏輯錯誤： 開發人員以為臨時表被刪除了，其實實質上永遠不會執行DROP TABLE #temp_test這句SQL。存儲在tempdb的sysobjects表中的臨時表，其全名由 CREATE TABLE 語句中指定的表名和系統生成的數字尾碼組成。為了允許追加尾碼，為本地臨時表指定的table_name不能超過 116 個字元（關於為什麼是116字元，也是有原因的，此處不展開）
Both regular and delimited identifiers must contain from 1 through 128 characters. For local temporary tables, the identifier can have a maximum of 116 characters.
那麼為什麼資料庫自動會給臨時表這樣命名呢？ 因為本地臨時表是對當前連接（當前會話）可見的。但是任意會話都可以創建同樣名字的臨時表，那麼臨時表的元數據在資料庫內部存儲的時候，使用同樣的名字就不能定位和區別，所以設計上，為了區別不同會話下同樣命名的臨時表，在tempdb..sysobjects裡面臨時表會保存的是資料庫自動生成的名字（#臨時表名+下劃線+12位的十六進位字元），如下測試所示，在另外一個會話中，我們創建一個同樣名字的臨時表，然後我們去資料庫檢查，你會發現tempdb..sysobjects中有兩個對象，他們名字的尾碼是不一樣的。
SELECT @@SPID;
GO
CREATE TABLE #temp_test( id INT, name VARCHAR(32));
那麼要如何定位、查找當前會話是否創建了本地臨時表對象呢？ 其實使用OBJECT_ID函數就OK了：
IF OBJECT_ID('tempdb..#temp_test' ) IS NOT NULL DROP TABLE #temp_test;
那麼存儲過程中使用上述腳本刪除本地臨時表，然後重建同樣名字的臨時表就可以了嗎？ 事實告訴我們，雖然上面SQL可以找到當前會話創建的本地臨時表刪除，但是如果是在存儲過程裡面，使用這種方式，創建本地臨時表，然後刪除、創建，依然會遇到這個錯誤，如下測試所示：
CREATE PROCEDURE PRC_TEST
ASBEGIN IF OBJECT_ID('tempdb..#temp_test' ) IS NOT NULL DROP TABLE #temp_test;
CREATE TABLE #temp_test( id INT, name VARCHAR(32));
INSERT INTO #temp_test VALUES(10, 'jimmy');
IF OBJECT_ID('tempdb..#temp_test' ) IS NOT NULL DROP TABLE #temp_test;
CREATE TABLE #temp_test( id INT, name VARCHAR(32));
INSERT INTO #temp_test VALUES(100, 'kerry');
DROP TABLE #temp_test;
ENDGO
其實在一個批處理裡面執行下麵SQL語句也會報錯，但是改寫一下SQL，在刪除後面加上一個GO語句，那麼這樣是不會報錯的(但是你單個SQL，一條一條執行是不會報錯的).
CREATE TABLE #temp_test( id INT, name VARCHAR(32));
INSERT INTO #temp_test VALUES(10, 'jimmy');
IF OBJECT_ID('tempdb..#temp_test' ) IS NOT NULL DROP TABLE #temp_test;
CREATE TABLE #temp_test( id INT, name VARCHAR(32));
INSERT INTO #temp_test VALUES(100, 'kerry');
DROP TABLE #temp_test;
