Hunterpo's IT Vision

歡迎一起分享 C#、T-SQL、ASP.NET 開發心得

2009-08-25

筆記 - 探究 SQL Server 中以 sp_ 前置詞命名之預存程序的運作行為

筆記 - 探究 SQL Server 中以 sp_ 前置詞命名之預存程序的運作行為

大家應該都知道，SQL Server 有內建許多系統預存程序，許多管理和參考活動，都可以利用系統預存程序加以執行。在 SQL Server 2005 中，系統預存程序實體是儲存於內部隱藏的資源 (Resource) 資料庫，邏輯上會出現在每個系統自訂和使用者自訂資料庫的 sys 結構描述中，且會有 sp_ 前置詞。本文的目的不是要介紹系統預存程序或其用法，而是要探討 sp_ 前置詞所建立的預存程序，有著甚麼樣的特殊行為，以及為什麼 Microsoft 強烈建議不要以 sp_ 前置詞建立自訂的預存程序…其中的一個重要原因是，SQL Server 使用這個前置詞來標明系統預存程序，但其中是否有更具有說服力的說法呢？讓我們往下看…


首先，根據前述的說法(摘錄自線上手冊)，系統預存程序邏輯上會存在每一個資料庫 (不管系統內建的或使用者自訂的) 的 sys 結構描述中，例如執行底下指令碼可以分別查詢 pubs、Northwind 資料庫所有的資料表資訊： 

EXEC sys.sp_tables;
GO

USE Northwind;
EXEC sys.sp_tables;
GO

執行結果如下：

sp_tables

可以看到 sp_tables 實際上在任一資料庫都可以執行，這是系統預存程序具有的獨特行為。


接下來，我們在 master 資料庫底下，自行建立 sp_proc1 預存程序，一開始印出資料庫名稱以確定現行資料庫環境，然後分別以動態跟靜態方式查詢 INFORMATION_SCHEMA.TABLES： 

USE master;
GO

IF object_id('dbo.sp_proc1') IS NOT NULL
	DROP PROC dbo.sp_proc1;
GO

CREATE PROC dbo.sp_proc1
AS
PRINT 'master.dbo.sp_proc1 executing in ' + DB_NAME();

-- 動態查詢
EXEC('SELECT TOP 3 TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ''BASE TABLE'';')

-- 靜態查詢
SELECT TOP 3 TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
GO


以這種方式建立的預存程序具有特殊行為，其特點為連線到不同資料庫時，仍然可以執行 (不必以資料庫限定名稱)，但有一個問題，運作行為變得難以捉摸，請看底下執行結果：

sp_proc1

動態 SQL 是採用現行資料庫的相關環境，因此我們的預存程序中，EXEC 命令回傳來自 Northwind 的資料表名稱，但靜態查詢的部分似乎「認為」master 資料庫才是它的執行環境 -- 它傳回來自 master 的資料表名稱(*註)。為了更進一步確定 sp_前置詞的魔力，我們將 master 底下的 dbo.sp_proc1 透過 SSMS 按滑鼠右鍵直接重新命名為 dbo.usp_proc1，然後分別在 Northwind、pubs、master 裡叫用：

usp_proc1

僅在 master 資料庫裡才能順利執行，Northwind、pubs 裡都出現「找不到預存程序 'dbo.usp_proc1'。」的錯誤訊息。


最後，我們透過 SSMS 按滑鼠右鍵再度將 master 底下的 dbo.usp_proc1 重新命名回 dbo.sp_proc1，然後於 Northwind 資料庫底下也建立一個 dbo.sp_proc1 的預存程序，指令碼如下，可以看到執行內容除了第 2 行以及第 11 行，將資料庫改為 Northwind 以外，其餘完全相同 master 底下的 dbo.sp_proc1： 

USE Northwind;
GO

IF object_id('dbo.sp_proc1') IS NOT NULL
	DROP PROC dbo.sp_proc1;
GO

CREATE PROC dbo.sp_proc1
AS
PRINT 'Northwind.dbo.sp_proc1 executing in ' + DB_NAME();

-- 動態查詢
EXEC('SELECT TOP 3 TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ''BASE TABLE'';')

-- 靜態查詢
SELECT TOP 3 TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
GO


隨後執行如下查詢並觀察結果：

sp_proc1_local

注意，我們先前的執行結果是 master.dbo.sp_proc1 內的靜態查詢會傳回 master 的資料表名稱，但現在於 Northwind 執行的結果卻是回傳 Northwind 所屬的資料表名稱，並且執行後的回傳訊息也明確指出確實是叫用 Northwind 本地端預存程序：

sp_proc1_local_message


本文一開始說明了 SQL Server 系統預存程序的獨特行為，接著測試在 master 資料庫底下以 sp_ 前置詞命名自訂的預存程序的運作方式以及問題，最後提醒你，假如在自有的資料底下建立的預存程序，與系統預存程序具有相同結構描述 (Schema) 與名稱的話，會被呼叫的是自訂版本的程序，以上種種理由看起來，我們可以理解何以 Microsoft 強烈建議要避免使用者定義預存程序使用 sp_ 前置詞來命名，如果實務上你仍然需要這麼做，那至少你應該知道會遭遇到哪些問題，並想辦法避免才是。


*備註：
我在看 Inside Microsoft SQL Server 2005：T-SQL Programming 這本書的時候，其中有提到 sp_MS_marksystemobject 程序，可以把自訂程序標記為系統型。但非常不建議在實務環境上這麼做…因為此程序並沒有正式文件說明，遇到問題時無法得到任何支援，並且在相容性上也有疑慮，所以放在備註欄當補充資料。

接續本文在 master 資料庫底下，自行建立 sp_proc1 預存程序之後的環境，我們以下列指令將其標註為系統型預存程序： 

USE master;
EXEC sys.sp_MS_marksystemobject 'dbo.sp_proc1';
GO


你會發現 master 底下的 dbo.sp_proc1 被歸類到系統預存程序了，再度由 Northwind、pubs 叫用此程序時，程序中所有的陳述式都會採用現行資料庫環境：

sp_proc1_mark sp_proc1_mark_message

這樣的行為已經跟系統預存程序沒什麼兩樣了。至於要不要這樣用各位看官或許可以在網路上用 "sp_MS_marksystemobject" 關鍵字搜尋一下，自行決定吧！


系列文章

標籤雲