Why developers should avoid creating SQL Server Stored Procedure prefix SP_

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:

• Performance improvement

• 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.

Conclusion

• 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]