用户在SQL Server中定义存储过程 [英] user define stored procedure in sql server

问题描述

如果我们创建带有sp_前缀的用户定义存储过程，是否会对性能产生影响?

if we create user define stored procedure with the prefix of sp_, does it effect on performance? it will check in master database first or user define database?

推荐答案

如果存储过程名称以"SP_"开头，则SQL Server首先在master数据库中搜索然后在当前会话数据库中.如果在主数据库中找到另一个具有相同名称的存储过程，则在主数据库中进行搜索会导致额外的开销，甚至会导致错误的结果.

SQL Server始终按以下顺序查找以sp_开头的存储过程:

1.主数据库中的存储过程.
2.基于提供的任何限定符(数据库名称或所有者)的存储过程.
3.如果未指定，则使用dbo作为所有者的存储过程.因此，尽管在当前数据库中可能存在以sp_为前缀的用户创建的存储过程，但是即使存储过程已使用数据库名称进行了限定，也始终会首先检查master数据库.

重要:如果任何用户创建的存储过程与系统存储过程具有相同的名称，则将永远不会执行用户创建的存储过程.

If a stored procedure name begins with "SP_," then SQL server first searches in the master database and then in the current session database. Searching in the master database causes extra overhead and even a wrong result if another stored procedure with the same name is found in master database.

SQL Server always looks for a stored procedure beginning with sp_ in this order:

1. The stored procedure in the master database.
2. The stored procedure based on any qualifiers provided (database name or owner).
3. The stored procedure using dbo as the owner, if one is not specified. Therefore, although the user-created stored procedure prefixed with sp_ may exist in the current database, the master database is always checked first, even if the stored procedure is qualified with the database name.

Important : If any user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.

是的将影响性能，这将减慢执行速度，并首先搜索系统过程！！！！！

Yes it will effect the performance, it will slow down the execution and also search system procedure first!!!

我们建议您不要使用sp_作为前缀来创建任何存储过程.SQL Server使用sp_前缀指定系统存储过程."

http://msdn.microsoft.com/en-us/library/ms190669.aspx [ ^ ]

"We recommend that you do not create any stored procedures using sp_ as a prefix. SQL Server uses the sp_ prefix to designate system stored procedures."

http://msdn.microsoft.com/en-us/library/ms190669.aspx[^]

这篇关于用户在SQL Server中定义存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！