Malaika Consultants LLC

NEVER EVER prefix your stored procedures with sp_ !

by Swanand Mokashi 27. January 2011 03:30

Recently I was doing some code review and saw that one of the developers called a new SQL Server stored procedure sp_GetSomething. I explained to her that it is not a good practice to do so but thought I might write a blog so it is useful to other programmers out there as well.

So why should you not name your stored procedure with sp_? Here is why :

This is from SQL Server Books OnLine (BOL) – search for "Creating a Stored Procedure""

“System Stored Procedures

Many of your administrative activities in Microsoft® SQL Server are performed through a special kind of procedure known as a system stored procedure. System stored procedures are created and stored in the master database and have the sp_ prefix. System stored procedures can be executed from any database without having to qualify the stored procedure name fully using the database name master.

It is strongly recommended that you do not create any stored procedures using sp_ as a prefix. SQL Server always looks for a stored procedure beginning with sp_ in this order:

  1. The stored procedure in the master database.
  2. The stored procedure based on any qualifiers provided (database name or owner).
  3. The stored procedure using dbo as the owner, if one is not specified.

Therefore, although the user-created stored procedure prefixed with sp_ may exist in the current database, the master database is always checked first, even if the stored procedure is qualified with the database name.

Important If any user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.”

See that ? So it has the following disadvantages:

  1. There is a little performance hit since the sql server processor needs to go through the above list before it arrives at your stored procedure
  2. There may be a stored procedure in the master database by the same name as yours and hence your stored procedure will not execute
  3. Say you named your stored procedure something generic like sp_BackupData. Microsoft may choose the same name in their future SQL Server release and then the disadvantage mentioned in #2 above will materialize

As a general guidance give your stored procedures meaningful names and if you have to add a prefix, add the prefix based on the functionality group that the SP belongs to. Example : CUST_GetCustomerDetails. We will cover SQL Server naming conventions in one of the future blog posts

Hopefully this was helpful.

Technorati Tags: ,,

Tags: ,

Database

Comments

1/27/2011 3:33:46 AM #

NEVER EVER prefix your stored procedures with sp_ !

You've been kicked (a good thing) - Trackback from DotNetKicks.com

DotNetKicks.com | Reply

1/27/2011 6:08:20 AM #

excellent article! Just LOVE it!
I wish I know this earlier.....

Ronaldo Australia | Reply

2/1/2011 5:55:35 PM #

I find it scary that people still do this.

Brad United States | Reply

Add comment




  Country flag

biuquote
  • Comment
  • Preview
Loading



::: | © Malaika Consultants LLC | :::

Malaika Consultants LLC

Malaika Consultants LLC is a custom software development consulting firm located in Cary (Raleigh Durham Research Triangle AKA RTP area ) North Carolina. We offer our expert consulting services in the Microsoft .NET and related technologies. Our mission is to partner with you and ensure the success of your project. We have a team in the USA that will help you with your Information Technology needs and we strive to be truly "Your Information Technology Angels"

We are offering 1 hour of free consulting -- ASP.NET, SQL Server, IIS, anything. Contact us to get your free consulting

Recent Comments

Comment RSS