Why You Shouldn’t Prefix “sp_” Before Your Stored Procedures in SQL Server?
Hello everyone. In this article, I will try to give information about why you should not add “sp_” prefix in front of your Stored Procedures in SQL Server.
Most people tend to prefix “sp_” to the name of the Stored Procedure when creating a Stored Procedure in SQL Server.
There are several reasons why this is considered bad practice.
Microsoft says you shouldn’t do this.
You can read Microsoft’s statement below.
Avoid using the sp_ prefix when naming Stored Procedures. This prefix is used by Microsoft SQL Server to identify system procedures. Using the prefix can break the application code if there is a system procedure with the same name.
This means that if you accidentally name your Stored Procedure with the same name as one of SQL Server’s Stored Procedure, your Stored Procedure will not be called. Instead, the SQL Server System Stored Procedure is called.
For example, if you have created a Stored Procedure named “sp_configure” in your database, it will never be executed when you call it because there is a system Stored Procedure named “sp_configure” in the master database and will be called instead.
Second, when SQL Server sees the prefix “sp_” at the beginning of a Stored Procedure, it first tries to find that procedure in the master database. As stated in the Microsoft documentation above, ” This prefix is used by SQL Server to denote system procedures “, so when SQL Server sees the phrase “sp_” prefix, it starts searching for system procedures. But after searching all the procedures in the master database and determining that your procedure is not there, it will return to your database to try to find the stored procedure.
As noted in the above post, procedures named with the “sp_” prefix will run slower. Of course, this may not always be noticed.
There are times when the above situation is an exception.
Use DatabaseOrnek
EXEC dbo.sp_configureile
Use DatabaseOrnek
EXEC DatabaseOrnek.dbo.sp_configureThe second example will call your procedure correctly.
But for this to work, you have to call it like (databasename.semaname.procedure) every time.
Unless you are intentionally creating a Stored Procedure in the SQL Server system database, there is no valid reason to name the Stored Procedure with the “sp_” prefix. It is not very convenient to send SQL Server to wrong database to find Stored Procedure. It’s confusing, inefficient, and can cause performance issues. So why take the risk? To help maintain peak performance of your databases, avoid using the “sp_” prefix in your Stored Procedure names.
Good luck to everyone in business and life.
