SQL Server 物件引用機制：延遲名稱解析、編譯時驗證與執行時行為

SQL Server 允許預存程序在建立時引用尚不存在的物件 (資料表、視圖等)，這項特性稱為「延遲名稱解析」。本文深度解析其運作機制、相容性層級影響、游標輸出參數規則、臨時預存程序與自動啟動程序等進階主題。

核心機制：建立時語法檢查，執行時編譯解析

-- 合法：建立時 test_table 不存在
CREATE PROC usp_TestDeferred
AS
SELECT * FROM test_table;  -- 僅語法檢查通過
GO

-- 執行時若 test_table 不存在 → 錯誤 208: Invalid object name
EXEC usp_TestDeferred;

流程圖解

CREATE PROCEDURE
    ↓
語法解析 (Parser) → 生成解析樹
    ↓
儲存 sys.sql_modules / sys.objects
    ↓ (不檢查物件存在性)
執行 EXEC
    ↓
計畫快取查找
    ↓ (無有效計畫)
編譯
    ↓
解析所有物件名稱 (Name Resolution)
    ↓ (不存在 → 錯誤 208)
生成執行計畫 → 快取 → 執行

相容性層級對延遲解析的影響

相容性層級	行為
65 (SQL 6.5 相容)	程序內建立的暫存表引用會在建立時警告，執行時若不存在報錯
70+ (SQL 7.0+)	完全延遲解析，建立時無警告，執行時才驗證

查看/設定：EXEC sp_dbcmptlevel 'dbname', 90 (90 = SQL 2005)

游標資料類型參數規則

• 僅能用於 OUTPUT 參數
• 必須同時指定 VARYING 與 OUTPUT 關鍵字
• VARYING 關鍵字暗示資料類型為 CURSOR

CREATE PROC usp_GetCursor @out_cursor CURSOR VARYING OUTPUT
AS
SET @out_cursor = CURSOR FOR SELECT * FROM sys.objects;
OPEN @out_cursor;
GO

游標輸出參數行為規則

游標類型	存儲程序結束時游標位置	返回給呼叫端的結果集
FORWARD_ONLY (只進)	第 N 行之後	第 N+1 行到結尾
FORWARD_ONLY	第一行之前	完整結果集
FORWARD_ONLY	最後一行之後	空結果集 (非 NULL)
SCROLL (可捲動)	任意位置	完整結果集，保留位置
任意類型	CLOSED 狀態	NULL

關鍵：關閉狀態僅在返回時生效，程序內可關閉後重開再返回。

臨時預存程序

類型	前綴	可見範圍	清理時機
區域臨時	#proc_name	僅建立連線	Session 結束自動清除
全域臨時	##proc_name	所有連線	最後使用 Session 結束清除

• 建立於 tempdb，任何用戶可建立
• 區域臨時程序權限不可授予他人
• 全域臨時程序權限不可撤銷
• 頻繁使用會導致 tempdb 系統表爭用，建議改用 sp_executesql

自動啟動預存程序

• SQL Server 啟動後、資料庫恢復完成後自動執行
• 必須在 master 資料庫建立，由 sysadmin 建立
• 無輸入參數，作為背景程序運行
• 啟動追蹤旗標 4022 或最低配置啟動 (-f) 可跳過執行
• 管理：sp_procoption @ProcName, 'startup', 'true/false'

巢狀預存程序與嵌套層級

• 最大嵌套層級：**32 層**
• 超過 32 層 → 整個呼叫鏈失敗
• 查看目前層級：SELECT @@NESTLEVEL
• 觸發器也算嵌套層級

估算編譯後預存程序大小

使用效能監視器計數器：

物件	計數器
SQLServer:Buffer Manager	Cache Size (pages)
SQLServer:Cache Manager	Cache Hit Ratio

總結

SQL Server 物件引用機制核心在於編譯時解析、執行時驗證。延遲名稱解析提供開發彈性，但也意味著部署前必須完整執行測試驗證所有物件存在。理解游標輸出參數規則、臨時程序生命週期、自動啟動機制與嵌套限制，是編寫健壯預存程序的關鍵。