One nasty habit that seems to persist throughout the ages with developers (I even did it myself) is the tendency to put “sp_” at the beginning of stored procedure names in SQL Server. There are actually several reasons this is considered a bad practice.
For starters, Microsoft says not to do it
According to the documentation, Microsoft states:
Avoid the use of the sp_ prefix when naming procedures. This prefix is used by SQL Server to designate system procedures. Using the prefix can cause application code to break if there is a system procedure with the same name.
A user-defined stored procedure that has the same name as a system stored procedure and is either nonqualified or is in the dbo schema will never be executed; the system stored procedure will always execute instead.
What this means is, if you accidentally name your stored procedure the same thing as one of SQL Server’s system procedures, your stored procedure will not get called. Instead, SQL Server will reference the system procedure. Every time.
So, for example, if you created a stored procedure named “sp_configure” in your database, it will never get executed when you call it because there is a system stored procedure in the master database named “sp_configure” and it will get called instead.
It sends SQL Server on a wild goose chase
Whenever SQL Server sees “sp_” at the beginning of a stored procedure, it first tries to find the procedure in the master database. As stated in the Microsoft documentation above, “This prefix is used by SQL Server to designate system procedures“, so when SQL Server sees “sp_” it starts looking for system procedures. Only after it has searched through all of the procedures in the master database and determined that your procedure is not there will it then come back to your database to try to locate the stored procedure.
It can cause performance problems
As alluded to in the previous point, procedures named with “sp_” are going to perform slower. It may not always be noticeable, but it is there. Connecting to DatabaseA, jumping over to the master database and scanning every stored procedure there, then coming back to DatabaseA and executing the procedure is always going to take more time than just connecting to DatabaseA and executing the procedure.
There are exceptions
As mentioned above, “sp_” tells SQL Server to look for your stored procedure in the master database. However, if you explicitly specify the database where the procedure is, SQL Server can go straight to it without first checking in the master database.
So this code:
And this code:
Will yield two separate results, with the second example actually calling your procedure correctly. However you will have to call it this way (database.schema.proc) every time in order for this to work.
Also, if you are creating or using a procedure like sp_whoisactive that you intend to run in the master database, but would like for it to be easily accessed from other databases, then it’s perfectly acceptable to use “sp_”.
The Bottom Line…
There is no valid reason to name a stored procedure with the “sp_” prefix, unless you are intentionally creating a procedure in the master database. Sending SQL Server to the wrong database to find your stored procedure is not very optimal. It is confusing, inefficient, and can cause performance problems. So why take chances? Simply avoid the use of “sp_” in your stored procedure names to help keep your databases running a peak performance.
For a more detailed explanation of why you shouldn’t use “sp_”, see the below resources:
- Is the sp_ prefix still a no-no?
- MSDN – CREATE PROCEDURE (Transact-SQL)
- MSDN – Creating Stored Procedures