The Meaning of Stored Procedures. Part 8 — The case of “sp_” prefix and a geek riddle

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:

USE master;

GO


CREATE PROC dbo.sp_test AS

PRINT ''master.dbo.sp_test executing in '' + DB_NAME();

GO


USE AdventureWorks2008R2;

EXEC dbo.sp_test;

USE tempdb;

EXEC dbo.sp_test;

EXEC AdventureWorks2008R2.dbo.sp_test;

GO

------

master.dbo.sp_test executing in AdventureWorks2008R2

master.dbo.sp_test executing in tempdb

master.dbo.sp_test executing in AdventureWorks2008R2

The important part is that in SQL Server 2005 onwards, if an object exists in the current database, it will be used by default:

USE AdventureWorks2008R2

GO

CREATE PROC dbo.sp_test AS

PRINT ''AdventureWorks2008R2.dbo.sp_test executing in '' + DB_NAME();

GO


USE AdventureWorks2008R2;

EXEC dbo.sp_test;

USE tempdb;

EXEC dbo.sp_test;

EXEC AdventureWorks2008R2.dbo.sp_test;

GO

------

AdventureWorks2008R2.dbo.sp_test executing in AdventureWorks2008R2

master.dbo.sp_test executing in tempdb

AdventureWorks2008R2.dbo.sp_test executing in AdventureWorks2008R2

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:

USE master;

GO


CREATE PROC dbo.sp_HRProc

AS

PRINT ''master.dbo.sp_HRProc executing in '' + DB_NAME();

SELECT * FROM HumanResources.Department;

GO


USE AdventureWorks2008R2;

EXEC dbo.sp_HRProc;

GO

------

master.dbo.sp_HRProc executing in AdventureWorks2008R2

Msg 208, Level 16, State 1, Procedure sp_HRProc, Line 5

Invalid object name ''HumanResources.Department''.

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:

USE master

GO

EXEC sp_MS_marksystemobject ''dbo.sp_HRProc'';

GO


USE AdventureWorks2008R2;

EXEC dbo.sp_HRProc;

GO

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.

Case study

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):

ALTER PROCEDURE [dbo].[sp_Proc]

    @Par1 nvarchar (10),

    @Par2 nvarchar (20),

    ...

    @Par25 smalldatetime

AS

    DECLARE @Var1 int

    DECLARE @Var2 as smalldatetime

    SELECT @Var2 = getdate()

...


SELECT TOP 1  @Par1 = Col1 FROM Tab ORDER BY Col1 DESC

INSERT Tab VALUES ( @Par1, @Par2, ... )

SELECT  @Var1+1

...

GO

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:

ALTER PROCEDURE [dbo].[sp_Proc]

    @Par1 nvarchar (10),

    @Par2 nvarchar (20),

    ...

    @Par25 smalldatetime

AS

    DECLARE @Var1 int

    DECLARE @Var2 as smalldatetime

    SELECT @Var2 = getdate()

...


BEGIN TRAN

SELECT TOP 1  @Par1 = Col1 FROM Tab WITH(UPDLOCK) ORDER BY Col1 DESC

INSERT Tab VALUES ( @Par1, @Par2, ... )

SELECT  @Var1+1

COMMIT TRAN

GO

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…

Share this article ...

Google Plus
Ihren LinkedIn Kontakten zeigen



This entry was posted in SQL Server, Stored Procedures and tagged , , , by Marcin Szeliga. Bookmark the permalink.
Marcin Szeliga

About Marcin Szeliga

Since 2006 invariably awarded Microsoft Most Valuable Professional title in the SQL category. A consultant, lecturer, authorized Microsoft trainer with 15 years’ experience, and a database systems architect. He prepared Microsoft partners for the upgrade to SQL Server 2008 and 2012 versions within the Train to Trainers program. A speaker at numerous conferences, including Microsoft Technology Summit, SQL Saturday, SQL Day, Microsoft Security Summit, Heroes Happen {Here}, as well as at user groups meetings. The author of many books and articles devoted to SQL Server.

Comments are closed.