Naming Convention of Stored Procedures

Question

Naming Convention of Stored Procedures

Joe Contreras-290946

SSCommitted

Points: 1903
More actions
November 29, 2007 at 12:32 pm

#62635

Hi All,
I'd like to follow best database practices. Today I was told that all stored procedures need to be prefix with a "sp_". In all the articles I've read in the past years, this is a big no.
Most articles suggest using NameOfProgramDBFunction for example FinanceInsARDailyData. I've been using this naming convention for years and have never used sp_.
However as I mentioned today I was basically reprimanded to use sp_.... and going forward I need to use this naming convention. Without getting into a long drawn out debate, I want to present a concrete case why this is wrong.
I have this from an article written here, but I sense a rebuttal. Are there other concrete examples I can use to present why not to use sp_.
"Never prefix a stored procedure with "sp_". This will cause a performance hit against your system as SQL Server always searches the Master database for these stored procedures first."
Thanks for your time,

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply

brendt hess Hall of Fame Points: 3256 More actions · Answer 1

Joe Contreras (11/29/2007)
Hi All,
....
I have this from an article written here, but I sense a rebuttal. Are there other concrete examples I can use to present why not to use sp_.
"Never prefix a stored procedure with "sp_". This will cause a performance hit against your system as SQL Server always searches the Master database for these stored procedures first."
Thanks for your time,

There are multiple sources on Microsoft's own site (and in books on line) to back this up. As an example, from http://support.microsoft.com/kb/263889 in Microsoft's Knowledge Base (notes at the bottom):

Note If your stored procedure name begins with the "sp_" prefix and is not in the master database, you see SP:CacheMiss before the cache hit for each execution even if you owner-qualify the stored procedure. This is because the sp_ prefix tells SQL Server that the stored procedure is a system stored procedure, and system stored procedures have different name resolution rules (the "preferred" location is in the master database). The names of user-created stored procedures should not begin with "sp_."

From Books-on-line for SQL 2000: (Look up Creating a Stored Procedure) at mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\createdb.chm::/cm_8_des_07_7yw5.htm

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.

There are many, many more. Just in general, it is a bad idea because it adds overhead to your system. If you must have a prefix, try 'usp_' (User stored procedure)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

Dang, Joe... between this and the NULLs problem, I'm thinking that you've landed in a doozy of a place. Who are these geniuses that you keep having fights about best practices with? I feel for ya...

Hmmm... lemme guess... they also want all table names to start with "tbl", too, huh?

Brendt dug up some pretty good URLs and is right about the sp_ recommendation of not being used in BOL... let us know if you need something else to shine a light in the ear of these apparent dimwits... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Joe Contreras-290946 SSCommitted Points: 1903 More actions · Answer 3

Thanks for the insight. I couldn't believe what I was hearing. What makes me laugh is the control issues I'm facing daily. "That's the way we do it here". I'm at the point in my age where I'm not going to argue. My only thought process is all this best practice information is available online and they don't bother reviewing the information. If this nonsense continues, I'm off to a new adventure.

To be honest, one of the reasons I'm still at this site is because I telecommute three days and attend meetings on the other two days oh and yeah I don't have a desk (LOL). I'm basically "squatting" the other two days.

Thanks,

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

"That's the way we do it here".

Heh... that reminds me of a saying...

"Even if the majority agrees on an idiotic idea... it's still an idiotic idea." 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Simon E Doubt SSCarpal Tunnel Points: 4191 More actions · Answer 5

Jeff Moden (11/29/2007)
Dang, Joe... between this and the NULLs problem, I'm thinking that you've landed in a doozy of a place. Who are these geniuses that you keep having fights about best practices with? I feel for ya...
Hmmm... lemme guess... they also want all table names to start with "tbl", too, huh?
Brendt dug up some pretty good URLs and is right about the sp_ recommendation of not being used in BOL... let us know if you need something else to shine a light in the ear of these apparent dimwits... 😉

Forgive my ignorance - why is the prefix tbl for tables a no-no?

Thanks,

Simon Doubt

Antares686 SSC Guru Points: 125444 More actions · Answer 6

Simon Doubt (11/30/2007)
Forgive my ignorance - why is the prefix tbl for tables a no-no?
Thanks,
Simon Doubt

The reality is this still boils down to personal preference however consider the impact of the name you give it.

Imagine you have a report that queries the table tbl_Reportdata but you decide to split the table for performance and DRI reasons. Now you report needs to talk to both tables so what all work has to be done to the application now. If you have no prefix then you could simply create two new tables for you data and make a view of the tables joined named Reportdata, this of course means your report does not have to be changed in order to manage this. If the system were and transaction application you could read from the view and just update the code of the app for updates, inserts and deletes only. Now you could say why not create a view named tbl_Reportdata, well now you borke your namin conventions consistency whihc is bad. So you can have cost bennefits and the reality is your app could care less about the type of object and names unless you impose a restrictive naming convention on the system.

The only reason to add tbl_ or other prefix is to support the idea of knowing what the object is, however that is a lazy persons way as all systems I am aware of have was to determine what an object is without this need.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

For me, it's because most things that you query are tables... for a view or a function, you might want to use the Hungarian notation to make it obvious that they are not tables.

Same thing with sprocs... how many other things can you use EXEC against (variables not-with-standing)? You know it's a sproc... why do you need something like "sp" as a prefix?

Heh... it's kinda like having a basket full of red balls... you don't need to tell someone to hand you a red ball, you just need to tell them to hand you a ball from the basket.

If someone want's to use the "tbl" prefix, that's fine, but I think that you should then follow Hungarian notation for everything... variables, tables, temp tables, sprocs, views, functions, etc, etc.

I guess it's really a matter of personal preference and a lot of us don't use Hungarian notation in most places in SQL. It's just that I hate that personal preference... too much typing of the obvious. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 8

And (missed it), Antares has even better reasons...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Simon E Doubt SSCarpal Tunnel Points: 4191 More actions · Answer 9

Thanks for the feedback, guys - I appreciate the insight.

Have a good weekend,

Simon Doubt

DBA, Fulcrum Publications, Inc.

Sergiy SSC Guru Points: 110206 More actions · Answer 10

to add my 2 cents. 🙂

Hungarian notation is good for compilers, but T-SQL is an interpreter.

Prefixes on names make it longer and inflate system indexes

It slows slows down interpreting of queries and recompilation of stored procedures.

It's probably looks like not a big deal but if the system uses many simple calls to DB (those who use "tbl" prefixes prefer to do data processing outside of database and do many simple calls to retrieve/store data) and if table names are not qualified with owner name ("dbo") then every call causes recompilation and in production environment where it's no a single "testuser" anymore it creates significant overhead.

Absolutely not necessary overhead.

_____________
Code for TallyGenerator

Carl Federl One Orange Chip Points: 25384 More actions · Answer 11

Take a look at Celko's "SQL Programming Style" where applicable to your question is section 1.2.3 "Avoid Descriptive Prefixes".

Who is the person specifying this convention? If they do not know that prefixing stored procedures with "sp_" is a very bad idea, what other poor decisions are they making ? What affect will these decision have on the project and on you?

Suggest you look at "Seven Tips for Effective Resume Writing" at

http://www.jobweb.com/resumesample.aspx?id=274

SQL = Scarcely Qualifies as a Language

Joe Contreras-290946 SSCommitted Points: 1903 More actions · Answer 12

I have no idea how much "political power" this DBA has. The issue I have with this person or anyone else is he feels having prefix on database objects is a good thing. I disagree with this. He is not my boss he controls the production databases and basically my pool my rules. I need to see and wait this out.

Thanks,

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 13

Heh... "A Developer must not guess... a Developer must KNOW"...

... especially in a political environment where you are challenging incumbent "knowledge".

One of the most difficult things you can do is to challenge an authority that's "always done it that way". Simply stated, you must change the way people think. In order to make a change there, you must show some ROI (Return on Investment). The only way you can do that is with a comparison test with repeatable and measurable outcomes.

Setup a test with 5 or 10 connections all having the same calls (in a loop of say 1000 calls) and one to measure things like sp_Who2, SysProcess, or maybe a Trace. Set them all up to "WAITFOR TIME 'hh:mm:ss:mmm' so they all kick off at the same time with the "measure" code kicking off a tiny bit after things start. Run the test several times against a stabile proc that begins with "sp_" and one that does not.

Only then will you have the knowledge and ROI proof that you need to overwhelm the incumbent methods.

As it did on your "NULL" problem, it may take some time to setup such a test... and it will take time to document the results. But, if you have strong feelings that something needs to be done, then you must do something that will prove your point with irrefutable evidence. Everything else is just "inadmissible, dismiss-able hear-say".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 14

As a compromise, you might prefix things with sp only, as in spMyProc. This doesn't cause the performance issues.

Or you could do uspMyProc, which is something I've seen. A short abbreviation could make sense if you are integrating other components. We added in some components years ago that had stored procs as "ifMyPRoc" and "lfMyProc" as methods to group all the relevent procs for a particular piece of functionality.

I tend to agree with Jeff that you know what you are querying, so it doesn't make sense to prefix things. The only place that it makes sense to add a postfix to me is with views. It can be helpful to know you're querying a view and potentially hitting multiple tables underneath.