Analysis of Problems easily caused by modifying function names in SQL SERVER

Analysis of Problems easily caused by modifying function names in SQL SERVER

1. Problem

Today, I encountered a strange problem: the function definition name queried by using sp_helptext XXX is different from the function name, and sp_helptext is actually used to query the System View sys. all_ SQL _modules. Directly query the definition field of this view and find that it is the same as sp_helptext. Is there a caching mechanism in the System View? Or a BUG? For the first problem, the situation was urgent and there was no time to verify whether the problem existed. The second problem is that I think there is no possibility. SQL server has evolved to this day (SQL 2016 official version is ready for release, and the Environment I use is SQL 2008 R2, hitting SP3 ), it is already a very mature system. Even if a BUG occurs, it is not something that I can find. It must be a mistake. So I turned to the database technology exchange group and soon some experts answered the question of renaming. I think of this function more than a week ago. Because of the test requirements, I changed the original function name through SSMS, and SQL SERVER won't update sys because of its name change. definition field in the all_ SQL _modules view! Therefore, the compiled functions are inconsistent with the function definitions in the sys. all_ SQL _modules System View.

2. Pay attention to and analyze problems

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

USE AdventureWorks2008R2;GOIF OBJECT_ID(N'dbo.ufn_test_1') IS NOT NULLBEGINDROP FUNCTION dbo.ufn_test_1;ENDGOCREATE FUNCTION dbo.ufn_test_1 ()RETURNS CHAR(1)ASBEGINRETURN ('F');ENDGO 

Code-1: Create Function dbo. ufn_test_1

In this case, use sp_helptext and sys. all_ SQL _modules to query. Everything is normal.

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: query the definition of the function dbo. ufn_test_1

Figure-1: query the definition of the dbo. ufn_test_1 function.

The name of SSMS is dbo. ufn_test_2.

Figure-2: Modify the function name

Query the definition of the function dbo. ufn_test_2. In this way, the compiled functions are inconsistent with the function definitions in the view! If you update the production server by using the definitions in the sp_helptext and sys. all_ SQL _modules queries, the problem will certainly occur.

3. Solutions and conclusions

The solution is also very simple. Just re-create this function. If you right-click SSMS to Modify (Modify) or generate the relevant Script (Script Function as) menu, the above problem will not occur. The same problems and solutions apply to stored procedures.

Conclusion:

(1) Try not to modify the object name. If you do want to modify it, rebuild it. If a table contains a large amount of data that needs to be rebuilt, it will be troublesome. Even if you modify the table name, there will be no problems such as functions and stored procedures, however, modifying the table name involves applications.

(2) Use the context menu of SSMS to modify or generate the object definition. However, if there are too many functions or stored procedures, it will be easier to find sp_helptext and sys. all_ SQL _modules. Check whether the object names are consistent. Here we mention that sp_helptext has some restrictions. For more information, see restrictions and extensions of print and sp_helptext in SQL.

Articles you may be interested in:

• SQL Server 2008 Manual modification of the table structure, the table cannot be saved and the Solution
• SQL Server trigger creation, deletion, modification, and viewing sample code
• SQL Server deletes stored procedures in batches and modifies stored procedures in batches
• Sqlserver adds, modifies, deletes, and describes fields.
• SQL statement used by sqlserver to modify the column name and table name
• Sqlserver2008 how to solve an invalid directory name error when viewing table records or modifying stored procedures
• SqlServer modify the storage location of database files and log files
• How to modify the auto-increment column value in the SQL Server Mysql database and solve the problem