Let me start the last part of the series with a simple question â€” What does the â€śsp_â€ť prefix stand for? If your immediate answer is â€śsystem stored procedures, of courseâ€ť you are only partially right. The better answer would be â€śsp stands for special prefixâ€ť. It might sound funny, but they are good reasons for this:
Â· Firstly, this prefix changes the way the object behave, so it is actually â€śspecialâ€ť.
Â· Secondly, this prefix can be used with other objects, like tables, and will change their behavior as well.
Nevertheless your first answer is absolutely understandable, mainly because most of the system stored procedures use this prefix. Likewise you will find all over the official documentation sentences like this: â€śIn SQL Server, the sp_ prefix designates system stored proceduresâ€ť (Naming issue SR0016: Avoid using sp_ as a prefix for stored procedures). This sentence is actually true, but your conclusions are not.
Why is the â€śsp_â€ť prefix so special?
Microsoft does use the â€śsp_â€ť prefix for their system stored procedures, but this is not the main reason. It comes down to the unique SQL Server behavior â€” it will look to the master database for any â€śsp_â€ť object, before â€śCould not find stored procedureâ€ť or â€śInvalid object nameâ€ť message will be thrown.
This behavior is simple to see:
The important part is that in SQL Server 2005 onwards, if an object exists in the current database, it will be used by default:
Lessons learned â€” In SQL Server 2005 onwards referring to objects that have â€śsp_â€ť prefix will NOT involve unavoidable name resolutions (that was the case in older SQL Server versions), so there is no performance penalty (remember â€” name resolutions means recompilations). In addition, the risk that the name of your procedure might conflict with the name of a system stored procedure (already existing or one that will be created in the future) is much smaller than it was in SQL Server 2000 and before.
Note. I donâ€™t recommend using â€śsp_â€ť prefix, all I say is that the consequences of using it are considerable smaller than before. Also, please, take a note that using three-part names (database.schema.object) will put you in a safe spot anyway.
How to benefit from this special behavior?
Ability to create objects (again, not only stored procedures but also i.e. tables) in the master database in a way that they will be accessible from all databases is quite useful. For example, you may think of maintenance procedures or logging tables. But there is one more thing to do â€” take this simple example:
Looks like to truly explore this â€śsp_â€ť prefix special behavior, objects not only should be in master database, they also have to be marked as system object:
Lesson learned â€” When you need a object to be accessible in the context of any user database, you”ll have to prefix it with â€śsp_â€ť, put it in the Master database, and mark it as system.
By now we dealt with both most frequently discussed problems related to â€śsp_â€ť prefix:
1. In SQL Server 2005 and above the local object will be used first, so there is no extra delay and no additional recompilations.
2. For the same reason the risk that instead of your procedure a system one will be called is marginal.
Now I would like to share with you a true story. It happened to one of our client and shows the real threat of using â€śsp_â€ť prefix.
Imagine a busy OLTP system that runs on SQL Server 2000. One of frequently called stored procedures looks like this (in reality, this procedure was bigger but for clarity I omitted the irrelevant part of the procedureâ€™s body):
Can you see the problem here? I bet you can. Nonetheless, remember that this procedure worked well for years, and only after the SQL Server was upgraded, the crazy assumption that it will be never called more than once in the same time kicked-off.
The immediate solution was to lock the row being read until the INSERT finished and the counter was incremented, so it might look like this:
Then the really intriguing question remains â€” why on earth this procedure worked in the first place? And why when the database was upgraded from SQL 2000 to SQL 2008, the errors start appearing?
If any of you would like to share their ideas, please put a comment to this post. I will read and answer all of them. I do hope that this way the riddle will be solvedâ€¦