SQL SERVER修改函数名容易引发的问题分析

这篇文章主要介绍了SQL SERVER修改函数名容易引发的问题分析的相关资料,需要的朋友可以参考下

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。

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

USE AdventureWorks2008R2;

GO

IF OBJECT_ID(N'dbo.ufn_test_1') IS NOT NULL

BEGIN

DROP FUNCTION dbo.ufn_test_1;

END

GO

CREATE FUNCTION dbo.ufn_test_1 ()

RETURNS CHAR(1)

AS

BEGIN

RETURN ('F');

END

GO

code-1: 创建函数dbo.ufn_test_1

这时,使用sp_helptext和sys.all_sql_modules查询,一切正常。

?

1

2

3

4

5

6

EXEC sp_helptext [dbo.ufn_test_1];

GO

SELECT OBJECT_ID('dbo.ufn_test_1') AS a, *

FROM sys.all_sql_modules

WHERE [object_id] = OBJECT_ID('dbo.ufn_test_1');

GO

code-2:查询函数dbo.ufn_test_1的定义

SQL SERVER修改函数名容易引发的问题分析

figure-1: 查询函数dbo.ufn_test_1的定义

在SSMS上直接改名为dbo.ufn_test_2。

SQL SERVER修改函数名容易引发的问题分析

figure-2: 修改函数名

再去查询函数dbo.ufn_test_2的定义。这样,就出现了已经编译好的函数跟在视图中的函数定义出现了不一致的情况!如果通过sp_helptext和sys.all_sql_modules查询出现的定义去更新生产服务器,就肯定会出现问题。

SQL SERVER修改函数名容易引发的问题分析

3. 解决与结论

解决方法也很简单,把这个函数重建即可。如果使用SSMS的右键修改(Modify)或生成相关脚本(Script Function as)的菜单,则不会出现以上的问题。同样的问题与解决方法,也适用于存储过程。

SQL SERVER修改函数名容易引发的问题分析

结论:

(1)尽量不要修改对象名,确实要修改的话,就重建吧。如果是表并且包含的大量数据要重建的话,就比较麻烦了,即使是修改表名不会出现像函数、存储过程的问题,但修改表名涉及应用程序等问题。

(2)尽量使用SSMS的右键菜单修改或生成对象的定义。但如果函数或存储过程太多,会觉得sp_helptext和sys.all_sql_modules会更方便些,查询出来的结果要认真核对下对象名是否一致即可。这里提一下,sp_helptext有些限制,可以参考SQL中print、sp_helptext的限制与扩展。

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/26747/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022-09-28 8:20:34
下一篇 2022-09-28 8:20:40

软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!