Everyone has an opinion on naming stored procedures. One opinion that can be agreed upon is that starting a stored procedure with “sp_” is not the way to go. Using “sp_” can only do harm. Let’s take a look at why that is.
Drunkard’s Search
When a stored procedure starting with “sp_” executes, SQL Server will not immediately run that stored procedure. Instead, it will first take the time to look in the master database for that stored procedure. If the stored procedure is not discovered in the master database, it will go to the stored procedure in the database where the process is being executed.
This isn’t a good check because it’s an extra step. There’s also the possibility (though probably rare) that you execute the wrong stored procedure.
What if you have a stored procedure outside of the master database with the same name as a stored procedure in master? We’ll look at that scenario below.
Prove It
Let’s take the sp_help stored procedure for example. If we run that procedure in my StackOverflow2013 database (ignore the extra junk), we’ll see the following:
USE StackOverflow2013;
GO
EXEC sp_help;
GO
SQL Server checked the master database and ran the sp_help stored procedure that it found.
Now, let’s create a new procedure in our StackOverflow2013 database that’s also called sp_help:
USE StackOverflow2013;
GO
CREATE PROCEDURE sp_help
AS
SET NOCOUNT ON;
SELECT 'We are asking for StackOverflow2013 help.'
GO;
If we run the following, will we see results based on the latest procedure we created?
USE StackOverflow2013;
GO
EXEC sp_help;
GO
Nope, we see still sp_help found in master because SQL Server went there first based on the “sp_” in the name.
Alternatives
So what prefix should you use instead? I can tell you not to trust what Google returns first:
Some people like “usp” or “usp_” for their names. Others might forgo a prefix altogether. Those options are all better compared to “sp_” which will only get you in trouble.
Thanks for reading!
Chad Callihan 
Just as a general design principle, a data element name should not include metadata. Putting the “sp_” prefix on a stored procedure name is like putting “i_” on the names of integer variables. Originally, with just one past simple compilers, we had to prefix things because symbol tables just work without help. This is why in the original Fortran, integers begin with the letters”I” thru “N” , why we have @, @@ #, and ## in the Sybase/SQL Server family. You also might remember the $ from the first versions of basic. The preferred form , the name of a procedure is _ with the subject of the sentence understood to be the invoking program
LikeLiked by 1 person
I find it helpful to prefix stored procedures with: proc_
Also note that I prefix functions with ft_ or fs_, depending on whether the function is returning a table or scalar. Views are prefixed with vw_. Etc.
When I am going back and reading code that I wrote many years ago, the reading/absorbing just goes much faster and easier to problem-solve when my objects and variables have prefixes. If something makes maintenance easier and helps the next person maintain code that I wrote, that’s a good thing in my book.
LikeLiked by 1 person
Using the “VW_” prefix on a viewed name is called a “Volkswagon”; the a fixed “_TBL” is called a “tibble”, etc. all of these nicknames are pejoratives for bad design.
Back in the days of Fortran, not even Fortran II, variables were not declared at the start of a module of code. Instead, any variable name began with the letters “I,” through “N” were known to be integers. This was part of the simple One Pass compiler we had to use all those decades ago. This simplified parser was carried over into BASIC and the length of a variable was limited to one character and strings had to begin with a $. This is why T SQL uses @, @@, # and ## prefixes on certain variable names.
In modern languages, there is a symbol table which can handle longer names and can look up the properties of the data elements. The current practice should be that the data element name identifies the data element, and contains no metadata. Repeat that do not embed metadata and data element names. If you’ve done a good job of naming your data elements, then the reader can figure out easily that “lookup_fobar ()” is a procedure or function. There is a followed by what essentially would be the object of the sentence.
When you include metadata in a name”, you have to watch out for things like “ifubar” , “sfubar”, “dpfubar” if you want to include the data type. Don’t worry they’ll all appear at some point in various programs in your system. Please try to think about your namespace at a global system-level and not in a module-by-module approach.
LikeLiked by 1 person
Joe Celko: I genuinely appreciate your knowledge of the history and how certain practices began. I find it interesting. And I appreciate you sharing that information.
However, I don’t use prefixes today because of some historical quirk. I use prefixes today, because I find prefixed code superior to dealing with code that doesn’t have prefixes – even when code/objects are given excellent names. In all the years I’ve been coding, I’ve never run into a problem with the prefixes. At the same time, I’ve found the prefixing practice immensely helpful. Maybe my brain just works differently than your brain does. Bottom line is that for my reality today, the pros of prefixing far outweigh any theoretical harms.
This is true the way I use prefixes anyway. I also appreciate the author who wrote this article to warn newbies not to use sp_ as the prefix. There’s clearly good reason to skip that one!
LikeLiked by 1 person
More decades ago than I like to think about, I worked for AIRMICS (Army Institute for Research in Management Information and Computer Science). We were doing experiments and research in the early days of software engineering. One of the areas that we looked at, via grants to universities doing experiments on students, involved making code more maintainable.
Department of Defense is probably still one of the largest users of software on earth, and 80% plus the cost of a system is in maintaining it. We had a whole bunch of projects funded mostly to the University of Maryland, but some independent contractors too. The most common experiment was to add known bugs to programs formatted in different ways, and then see how long it took programmers to find these deliberate bugs (if they could!). This was called”BE-bugging” instead of “debugging”. Back in those days, this meant COBOL, I moved to assume the general principles hold
We tried all uppercase, mixed case, camel case and just about everything else we could think of. This research led me to come up with the rules I’ve used for decades for formatting code, including not using the prefixes. When I recommend something I usually have at least something to support my opinion., Not just that’s the way I like to do it.
I started as a Fortran programmer in the 1960s and picked up a really bad habit. It took me years to shake. The first releases of Fortran allowed only six characters for a variable name, so I used a whole list of six-letter mail first names in my code (Melvin, George, Joseph, Irving, etc.) when I needed a dummy variable. I also grew up with MAD magazine you can see the influence of the names of their dummy characters in my code. I honestly could not see how stupid this was until I finally showed some of my code to another programmer. I hope the technique of code review with your peer group is still in place because it’ll find a lot of crap like this.
LikeLike