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.
And here:
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:
|
1 2 3 4 |
Use DatabaseA EXEC dbo.sp_configure |
And this code:
|
1 2 3 4 |
Use DatabaseA EXEC DatabaseA.dbo.sp_configure |
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.
Resources:
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
This is interesting. As you pointed out, SQL Server makes an assumption based on the name that lends itself to performance issues. I’m curious if there a similar performance issues with naming conventions in other areas? For example, I’ve seen a Widget table where every column name begins with “widget_” or indexes that all start with “ix_{tablename}”. These conventions make it difficult (for me at least) to separate the noise from the data. If there are 25 indexes that all start with “ix_widget_” how far do I have to go in the list to find the index on the “specialProperty” column, for example? But can there be tangible performance implications of such naming conventions?
Hey Scott! “sp_” is the only situation that I’m aware of where a naming convention can cause this sort of problem. I’ve never heard of problems like this with things like tables or views. There are schemas like “sys” that contain internal tables and views, but I don’t believe you can add anything to those.
As for indexes, SQL Server is coming at it from the “other end” so to speak. It bases its index choice for a query on its statistics of the data in the index. If you were querying “specialProperty” in the Widget table, SQL Server would see which index data sets (if any) contain “specialProperty”, then choose whichever one it feels most accurately supports the query, and references the “name” of that index for our benefit. At that point it already has the data from the index, and is just returning whatever name is assigned to the index.
I checked my local ms sql server and I don’t see any system store procedure within dbo schema – all of them reside in sys schema. I assume nobody would ever use ‘sys’ schema for “non-master” database and would never set ‘sys’ schema for any user. So, this leaves me with a question, how server can start search of “sp_***” object if all SPs in master are in ‘sys’ schema?
Good question! It would appear that SQL Server either checks all schemas in the system databases, or possibly it just translates everything over to “sys”.
For example, if you execute the following calls to the “sp_executesql” system procedure, they all return the same results, even though the actual procedure is in the “sys” schema:
EXEC sp_executesql N’SELECT @@version’;
EXEC dbo.sp_executesql N’SELECT @@version’;
EXEC sys.sp_executesql N’SELECT @@version’;