| Author |
Topic |
|
josethegeek
Starting Member
45 Posts |
 Posted - 2004-07-16 : 18:57:49
|
| 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
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-07-16 : 19:05:35
|
| 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 |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-17 : 04:05:21
|
| 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 |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-17 : 09:34:43
|
| 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.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-07-17 : 17:46:27
|
| 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. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-17 : 17:55:29
|
| 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 |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-17 : 21:02:12
|
| 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. :)MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-18 : 02:56:39
|
| But your current spot is going to impliment this RSN? ... Kristen |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-18 : 05:12:48
|
| My current spot uses APPFunctionSubFunctionVerb.It's a lot like what you have.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-18 : 06:01:13
|
| Blimey Derrick, you just going to bed after a heavy night out, or just got up?Kristen |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-18 : 13:21:56
|
| I never sleep. I'm the ultimate imsomniac.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2004-07-18 : 15:19:35
|
| Me, I use p_app_module_NounVerbI 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 |
|
|
|
|
Naming Store Procedures