SA0015 : Avoid 'sp_' prefix when naming stored procedures
Introduction
Section titled “Introduction”Using the sp_ prefix for custom stored procedures can lead to performance issues and confusion with system stored procedures.
Description
Section titled “Description”In SQL Server, stored procedures beginning with the sp_ prefix are reserved for system stored procedures. When a stored procedure with this prefix is called, SQL Server first searches for it in the master database before checking the user database and schema. This can result in unnecessary performance overhead and can cause confusion if a custom procedure inadvertently shares a name with a system procedure.
Problematic stored procedure declaration:
CREATE PROCEDURE sp_CustomProcedureASBEGIN SELECT * FROM MyTable;END;The above example illustrates the use of the sp_ prefix. SQL Server will first look for sp_CustomProcedure in the master database, possibly causing a delay if the procedure is intended to reside in a user database.
-
Increased execution time when SQL Server searches the master database first.
-
Risk of naming conflicts with existing or future system stored procedures.
How to fix
Section titled “How to fix”Avoid using the sp_ prefix when naming stored procedures to prevent performance issues and naming conflicts with system stored procedures.
Follow these steps to address the issue:
1.Identify stored procedures in your database that use the sp_ prefix. You can query the system tables to list all such procedures:
SELECT name FROM sys.procedures WHERE name LIKE 'sp_%';2.Rename each stored procedure to remove the sp_ prefix. Use a distinct and meaningful naming convention. You can use the sp_rename system stored procedure for renaming:
3.Execute the renaming operation for each problematic procedure:
EXEC sp_rename 'sp_CustomProcedure', 'CustomProcedure';4.Update any scripts, applications, or other dependencies that call the renamed stored procedures to use the new names.
For example, the corrected stored procedure declaration looks like this:
CREATE PROCEDURE CustomProcedureASBEGIN SELECT * FROM MyTable;END;The rule has a Batch scope and is applied only on the SQL script.
Parameters
Section titled “Parameters”Rule has no parameters.
Remarks
Section titled “Remarks”The rule does not need Analysis Context or SQL Connection.
Effort To Fix
Section titled “Effort To Fix”2 minutes per issue.
Categories
Section titled “Categories”Naming Rules, Bugs
Additional Information
Section titled “Additional Information”There is no additional info for this rule.
Example Test SQL
Section titled “Example Test SQL”-- The 'sp_' stored procedure name prefix is reserved for system procedures and should be avoidedCREATE PROCEDURE sp_uspGetAllEmployeesAS SET NOCOUNT ON; SELECT LastName, FirstName, JobTitle, Department FROM HumanResources.vEmployeeDepartment;Analysis Results
Section titled “Analysis Results”| Message | Line | Column | |
|---|---|---|---|
| 1 | SA0015 : Avoid ‘sp_’ prefix when naming stored procedures. | 2 | 17 |