優化：將變數聲明移動到過程的頂部

在優化一些儲存過程時，我與 DBA 坐下來，研究了一些具有高阻塞和/或高讀/寫活動的儲存過程。

DBA 提到的一件事是我應該TABLE在儲存過程的頂部聲明所有變數（尤其是變數）以避免重新編譯。

這是我第一次聽說這個，並且在重新訪問我們擁有的所有不同的儲存過程之前正在尋找一些確認。他稱其為“延遲查看程式碼”，並且重新編譯鎖定了導致阻塞的模式。

將所有變數聲明移動到儲存過程的頂部是否會減少重新編譯？

不。

這要麼在很久以前是正確的（現在不再是，至少從 SQL Server 2000 開始），或者它從來都不是正確的，您的 DBA 只是將他的建議與以下建議混淆了：

在儲存過程開始時將臨時表的所有 DDL 語句（如創建索引）組合在一起非常重要。通過將這些 DDL 語句放在一起，可以避免由於模式更改而導致的不必要的編譯。

您可以在此頁面上找到有關此建議背後原因的另一種解釋。

如果我們查看這個 Microsoft KB，我們會發現儲存過程重新編譯的原因可能是以下之一（SQL Server 2005+）：

1. 架構已更改。
2. 統計數據發生了變化。
3. 重新編譯 DNR。
4. 設置選項已更改。
5. 臨時表已更改。
6. 遠端行集已更改。
7. 對於瀏覽權限已更改。
8. 查詢通知環境已更改。
9. MPI 視圖已更改。
10. 游標選項已更改。
11. 帶有重新編譯選項。

聲明一個變數——甚至是一個表變數（即@table_variable）——顯然不能觸發任何這些事件，因為聲明一個變數不算作 DDL。變數（甚至是表變數）是專門用於 T-SQL 程式的臨時對象。這就是為什麼表變數沒有統計資訊並且不受事務約束的原因。 聲明一個變數（表與否）不能觸發 proc 重新編譯。

#temp_table然而，創建臨時表（即）或索引是影響數據庫物理定義的 DDL。臨時表和索引是具有統計資訊和事務控制的“真實”對象，因此創建它們可能會觸發上面列表中的任何事件 1、2 或 5，從而觸發 proc 重新編譯。

引用自：https://dba.stackexchange.com/questions/7477