SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Naming Store Procedures
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

josethegeek
Starting Member

45 Posts

Posted - 07/16/2004 :  18:57:49  Show Profile  Reply with Quote
Maybe someone can help me out...
Recently I was told that I should not be naming my stored procedures with sp_ at the beginning (ex. sp_addCustomer) because of performance issues since SQL Server thinks stored procedures beginning with sp_ are system stored procedures. Is this true?

Thanks,
Jose

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 07/16/2004 :  19:05:35  Show Profile  Reply with Quote
I don't think that there is a performance issue. All system sprocs start with sp_, xp_ or dt_. It makes it much easier to locate your own sprocs when you avoid using these prefixes.

A lot of people here use usp_, or isp_ or whatever. Many people don't prefix their sprocs at all. I think that vyas has a sql naming convention paper at his website.

yes, check this out for some more suggestions: http://vyaskn.tripod.com/object_naming.htm



-ec
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 07/17/2004 :  04:05:21  Show Profile  Reply with Quote
The "performance issue" is because SQL treats "sp_xxx" as being available in master, so it looks in master first, doesn't find it, then looks in the current database.

EDIT: There is also the fact that IF it exists in master you're hosed!

Kristen

Edited by - Kristen on 07/17/2004 04:05:52
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 07/17/2004 :  09:34:43  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
It's a recommended Best Practice by Microsoft to not use sp_ for your stored procedure. It does search in master first for them unless everytime you call them, you specify the database name. The only ones you should name with sp_ are stored procedures that sit in the master database and are used to perform maintenance on all databases. Some people don't even like doing that.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 07/17/2004 :  17:46:27  Show Profile  Reply with Quote
I've always used a prefix for what the primary purpose of the sproc is and all of them now have names prefixed by sel_, ins_ upd_ or del_. Works great for me.
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 07/17/2004 :  17:55:29  Show Profile  Reply with Quote
I do it the other way round - NounVerb - and I incorporate the primary table in the name (actually, our table names have a "nickname" componenet, so we use that to keep the associated names shorter, but that's by-the-by).

So I have AAA_SP_MMM_TTT_Save - where AAA=Aplication nickname, MMM=Module nickname, TTT=Table nickname and "Save" is the verb!

General idea of this is that an alpha-sort groups things together that relate the the same "main table" - but it aint foolproof of course.

Kristen
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 07/17/2004 :  21:02:12  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
I like to use app_funtion_description_idsu (idsu for insert, delete, update, and select)(function being part of app if applicable)(description being a table if applicable or functionality of what piece of function)(app being an abbreviation of app). Of course, I've only been two places where we've got this implemented and my stuff at home is a tragedy as it reflects everyone I've ever worked. :)


MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 07/18/2004 :  02:56:39  Show Profile  Reply with Quote
But your current spot is going to impliment this RSN? ...

Kristen
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 07/18/2004 :  05:12:48  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
My current spot uses APPFunctionSubFunctionVerb.

It's a lot like what you have.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 07/18/2004 :  06:01:13  Show Profile  Reply with Quote
Blimey Derrick, you just going to bed after a heavy night out, or just got up?

Kristen
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 07/18/2004 :  13:21:56  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
I never sleep. I'm the ultimate imsomniac.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

aiken
Aged Yak Warrior

USA
525 Posts

Posted - 07/18/2004 :  15:19:35  Show Profile  Send aiken an ICQ Message  Reply with Quote
Me, I use p_app_module_NounVerb

I try to avoid using specific tables, since to me these sp's should be business object centric, and the vast majority of mine act on more than one table. But they all serve to select / create / update / delete objects (like users, preferences, emails, etc).

I also think NounVerb makes more sense than VerbNoun, so in alphabetical listings the various functions against a single noun are grouped together.

I use the p_ preface to distinguish form UDF's, which I use f_ for.

But yes, do not use sp_ as a prefix. Beyond that, whatever you find intuitive and descriptive should be fine. Just be consistent and it will all be good.

Cheers
-b
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000