Is it a good idea for developers to create SQL Server stored procedures with prefix SP_?
Does the stored procedure name prefix(if you use it) matter? Will it hurt performance?
This blog looks at the SP_ prefix and understands why developers should avoid creating SP_ stored procedures.
As a general practice, developers create SQL Server Stored Procedure with the prefix SP_. A general recommendation is to avoid the SP_ prefix. This recommendation is for the following reasons:
Avoiding ambiguity with system catalog
Microsoft documentation also asks to avoid using the sp_ prefix for stored procedures.
Let's explore why you should avoid the SP_ prefix for SQL Server Stored Procedures.
SP_Who2 is a system stored procedure that returns information about sessions, commands, hostname, CPU, IO, and status. The query returns the following output for my system. You can note here that I specified Use TempDB to run it under the TempDB database context.
Suppose you created a user-defined stored procedure with a similar name in the TempDB.
Use Tempdb GO CREATE PROCEDURE Sp_Who2 AS BEGIN PRINT 'This is the output of SP_WHO2 Stored procedure created in TempDB'; END
Let's try to run this user-defined stored procedure created in the TempDB. Remember, we have two stored procedures with similar names.
System stored procedure
User-defined stored procedure
If a user-defined stored procedure has the same name as a system stored procedure, SQL Server always executes the system stored procedure. Therefore, you might not get the output you expect from a user-defined stored procedure since SQL Server executed the system stored procedure.
When you create a SQL Server Stored Procedure with SP_ prefix, SQL Server tries to find that procedure in the master database during execution. Once it finishes searching procedures in the master database and does not find it there, SQL Server checks in the specified database.
How does SP_ prefix store procedure affect performance
Let's create two stored procedures in the [AdventureWorks2019] database with and without the SP_ prefix. Both procedures return the record counts for the specific SalesOrderID.
Use AdventureWorks2019 GO CREATE or ALTER PROCEDURE Sp_Count_Records (@SalesOrderID int) AS BEGIN Select count(1) as Records from [Sales].[SalesOrderHeader] where SalesOrderID=@SalesOrderID END
Use AdventureWorks2019 GO CREATE or ALTER PROCEDURE Count_Records (@SalesOrderID int) AS BEGIN Select count(1) as Records from [Sales].[SalesOrderHeader] where SalesOrderID=@SalesOrderID END
The SQLQueryStress utility shows that 1,00,000 iterations took 1 min 42 seconds for SP with SP_ prefix.
While the same stored procedure without SP_ prefix took 1 minute and 35 seconds.
Although the difference in elapsed time for my sample stored procedure looks minimal. However, think of a frequently and resource-intensive executing procedure; even a slight difference might impact an application.
Avoid using sp_prefix for the SQL Server stored procedure name
Always use a fully qualified name for stored procedure execution, such as [database].[schema].[stored_procedure]