Problems caused by SQL Server modifying function names

1. Questions

Today, there is a strange problem: using sp_helptext XXX query out of the function definition name is different from that of the function name, and sp_helptext is actually query sys.all_sql_modules of this system view. Query the definition field of this view directly, and find the same as sp_helptext . Is there a mechanism for system view and caching? Or is it a bug? For the first question, the situation was urgent and there was no time to prove whether it existed. The second question, I think is not possible, SQL Server development today (SQL 2016 is ready to launch, I use the environment is SQL R2, hit SP3), is a very mature system, even if the bug is not my level of people can find, It must be where I got the wrong one. Then turned to the database technology Exchange Group, and soon the great God answered is the question of renaming. I think about it right away. One weeks ago, because of the need for testing, the original function name was changed through SSMS, and SQL Server did not update the definition of the sys.all_sql_modules View because of the renaming Field of! This resulted in inconsistencies between the compiled function and the function definition of the sys.all_sql_modules system view.

2. Focus on and analyze issues

Do a test to reproduce the problem. First, create a simple test function dbo.ufn_test_1.

 Useadventureworks2008r2;GOIF object_id(N'dbo.ufn_test_1') is  not NULLBEGIN    DROP FUNCTIONdbo.ufn_test_1;ENDGOCREATE FUNCTIONdbo.ufn_test_1 ()RETURNS CHAR(1) asBEGIN    RETURN('F');ENDGO

Code-1: Create function dbo.ufn_test_1

At this point, use sp_helptext and sys.all_sql_modules query, everything is OK.

EXECSp_helptext[dbo.ufn_test_1];GOSELECT object_id('dbo.ufn_test_1') asA*   fromSys.all_sql_modulesWHERE [object_id] = object_id('dbo.ufn_test_1');GO

Code-2: Definition of query function dbo.ufn_test_1

Figure-1: Definition of query function dbo.ufn_test_1

Renamed to dbo.ufn_test_2directly on SSMs.

Figure-2: Modify Function name

Then query the definition of function dbo.ufn_test_2 . In this way, there is an inconsistency between the compiled function and the function definition in the view! If you update the production server with the definition of sp_helptext and sys.all_sql_modules queries, there is a definite problem.

3. Settlement and conclusion

The solution is simple enough to reconstruct the function. If you use SSMs right-click to modify (Modify) or generate a menu of related scripts (script Function as), the above problem does not occur. The same problem applies to stored procedures as well.

Conclusion:

(1) Try not to modify the name of the object, if you really want to modify it, rebuild it. If it is a table and contains a lot of data to be rebuilt, it is more troublesome, even if the table name modification does not occur like functions, stored procedure problems, but modify the table name involves the application and other issues.

(2) Try to modify or generate the definition of an object using SSMs's right-click menu. But if the function or stored procedures too much, will feel sp_helptext and sys.all_sql_modules will be more convenient, query out the results to carefully check if the object name is consistent. Mention here, sp_helptext some restrictions, you can refer to my other blog about the extension of sp_helptext. (http://www.cnblogs.com/fishparadise/p/4797539.html)

Problems caused by SQL Server modifying function names