存儲過程中引用的常規表，臨時表以及表變數是否會導致存儲過程的重編譯

在存儲過程中，經常要引用一些表來存儲一些中間數據，用完即刪。對於這個中間表，用常規表，臨時表或者表變數有什麼區別呢？

下麵我們看一下這三種中間表是否會造成執行計劃的重編譯。

首先打開sql server profile，監控存儲過程。

1.建第一個存儲過程，在存儲過程中創建常規表TT1

CREATE PROCEDURE TEST1_PRO
AS 
BEGIN 
    CREATE TABLE TT1(
    ID INT IDENTITY,
    NAME VARCHAR(10)
    )

    INSERT INTO TT1
    VALUES ('name1')

    select * From TT1

    DROP TABLE TT1
END

下麵調用存儲過程

--Firsttime
EXEC TEST1_PRO

查看sql server profile的如下截圖:

可以看到在存儲過程中創建常規表，會造成兩次重編譯。第一個編譯是由於常規表的新建之後的DML操作造成的，第二個重編譯是查詢新建表導致的執行計劃的編譯。

下麵我們第二次調用該存儲過程

--SecondTime
EXEC TEST1_PRO

查看sql server profile的如下截圖:

可以看到第二次調用存儲過程跟第一次調用存儲過程都會造成兩次重編譯。

2.建第二個存儲過程，在存儲過程中創建臨時表#TT1

CREATE PROCEDURE TEST2_PRO
AS 
BEGIN 
    CREATE TABLE #TT1(
    ID INT IDENTITY,
    NAME VARCHAR(10)
    )

    INSERT INTO #TT1
    VALUES ('name1')

    select * From #TT1
    DROP TABLE #TT1
END

調用存儲過程

--Firsttime
EXEC TEST2_PRO

查看sql server profile的如下截圖:

可以看到第一次調用TEST2_PRO存儲過程跟第一次調用TEST1_PRO的時候造成的重編譯是一樣的，兩次重編譯。

我們再次調用該存儲過程

--SecondTime
EXEC TEST2_PRO

查看sql server profile的如下截圖:

可以看到當存儲過程再次被執行時，沒有發生重編譯。臨時表只能用於存儲過程範圍之內，無法在存儲過程範圍之外使用，因此其架構在多次執行之後並未發生任何改變，從而可以在存儲過程後續執行期間重用現有的執行計劃。

3.建第三個存儲過程，在存儲過程中創建表變數@TT1

CREATE PROCEDURE TEST3_PRO
AS 
BEGIN 
DECLARE @TT1 TABLE(
    ID INT IDENTITY,
    NAME VARCHAR(10)
    )

    INSERT INTO @TT1
    VALUES ('name1')

    select * From @TT1

END

調用存儲過程

--Firsttime
EXEC TEST3_PRO

查看sql server profile的如下截圖:

可以看到對於存儲過程中創建表變數，第一次調用該存儲過程並沒有發生重編譯。

再次調用存儲過程

--SecondTime
EXEC TEST3_PRO

查看sql server profile的如下截圖:

再次調用存儲過程之後，也沒有發生重編譯，可以看到當用表變數當存儲過程的中間表，不會造成存儲過程的重編譯。

由於表變數不會寫日誌，不會造成鎖開銷，不能在Declare之外創建主鍵索引等，因此表變數不會造成架構的變化，從而不會造成重編譯。該存儲過程的執行計劃已經在創建存儲過程的時候生成了，因此之後執行的存儲過程不會造成執行計劃的重編譯。

綜上所述，我認為在存儲過程中中間表需要存儲少量數據時，創建表變數來存儲中間數據是開銷最小的。

如果大家有什麼問題可以提出來，相互交流，謝謝！