SQL SERVER修改函數名引起的問題

標籤：t-sql編程

1. 問題

曾經遇到一個奇怪的問題：使用sp_helptext XXX查詢出來的函數定義名竟然跟函數名不同，而sp_helptext實際是查詢sys.all_sql_modules這個系統檢視表的。直接查詢這個視圖的definition欄位，發現跟sp_helptext是一樣的。難道是系統檢視表也存在緩衝之類的機制？或者是個BUG？對於第一個問題，當時情況緊急，沒有時間去求證是否存在了。第二個問題，我想沒什麼可能，SQL SERVER發展到今天（SQL 2016正式版準備推出，我使用的環境則是SQL 2008 R2，打了SP3），已經是很成熟的一個系統，即使是出現BUG也不是我這種水平的人能發現的，肯定是哪我哪裡弄錯了。於是求助於資料庫技術交流群，很快有大神回答了是改名的問題。我馬上就想起這個函數在一個多星期前，因為測試的需要，通過SSMS改了原函數名，而SQL SERVER不會因為改名去更新sys.all_sql_modules視圖的definition欄位的！於是就造成了已經編譯好的函數與sys.all_sql_modules系統檢視表的函數定義出現了不一致的情況。

2. 重現與分析問題

做一個測試來重現下問題。首先，建立一個簡單的測試函數dbo.ufn_test_1。

USE AdventureWorks2008R2;GOIF OBJECT_ID(N‘dbo.ufn_test_1‘) IS NOT NULLBEGIN    DROP FUNCTION dbo.ufn_test_1;ENDGOCREATE FUNCTION dbo.ufn_test_1 ()RETURNS CHAR(1)ASBEGIN    RETURN (‘F‘);ENDGO

Code-1: 建立函數dbo.ufn_test_1

 

這時，使用sp_helptext和sys.all_sql_modules查詢，一切正常。

EXEC sp_helptext [dbo.ufn_test_1];GOSELECT OBJECT_ID(‘dbo.ufn_test_1‘) AS a, *  FROM   sys.all_sql_modulesWHERE [object_id] = OBJECT_ID(‘dbo.ufn_test_1‘);GO

code-2:查詢函數dbo.ufn_test_1的定義

650) this.width=650;" src="http://s2.51cto.com/wyfs02/M00/7D/DC/wKiom1bxBKngnteoAAGv5LwqxYA152.jpg" title="01.jpg" alt="wKiom1bxBKngnteoAAGv5LwqxYA152.jpg" />

Figure-1: 查詢函數dbo.ufn_test_1的定義

650) this.width=650;" src="http://s1.51cto.com/wyfs02/M02/7D/DC/wKiom1bxBMrj9SfPAAHLCFD4Mg8278.jpg" title="02.jpg" alt="wKiom1bxBMrj9SfPAAHLCFD4Mg8278.jpg" />

Figure-2: 修改函數名

 

再去查詢函數dbo.ufn_test_2的定義。這樣，就出現了已經編譯好的函數跟在視圖中的函數定義出現了不一致的情況！如果通過sp_helptext和sys.all_sql_modules查詢出現的定義去更新生產伺服器，就肯定會出現問題。

650) this.width=650;" src="http://s4.51cto.com/wyfs02/M01/7D/D8/wKioL1bxBX6SlRMBAAGbCMoVEWo813.jpg" title="03.jpg" alt="wKioL1bxBX6SlRMBAAGbCMoVEWo813.jpg" />

Figure-3: 定義對比

3. 解決與結論

解決方案也很簡單，把這個函數重建即可。如果使用SSMS的右鍵修改（Modify）或產生相關指令碼（Script Function as）的菜單，則不會出現以上的問題。同樣的問題與解決方案，也適用於預存程序。

650) this.width=650;" src="http://s4.51cto.com/wyfs02/M00/7D/DC/wKiom1bxBQSjlrB-AAE8xMeB04s302.jpg" title="04.jpg" alt="wKiom1bxBQSjlrB-AAE8xMeB04s302.jpg" />

Figure-4: SSMS右鍵修改預存程序

結論：

（1）盡量不要修改對象名，確實要修改的話，就重建吧。如果是表並且包含的大量資料要重建的話，就比較麻煩了，即使是修改表名不會出現像函數、預存程序的問題，但修改表名涉及應用程式等問題。

（2）盡量使用SSMS的右鍵菜單修改或產生對象的定義。但如果函數或預存程序太多，會覺得sp_helptext和sys.all_sql_modules會更方便些，查詢出來的結果要認真核對下對象名是否一致即可。這裡提一下，sp_helptext有些限制，可以參考我的另一篇部落格關於sp_helptext的擴充：http://fishparadise.blog.51cto.com/11284420/1753941

本文出自 “FishParadise” 部落格，請務必保留此出處http://fishparadise.blog.51cto.com/11284420/1753943

SQL SERVER修改函數名引起的問題