July 9, 2009 at 7:07 am
Jeff Moden (7/8/2009)
So what's the advantage to prefixing every table name with "tbl"? I see it as a disadvantage just because it requires more typing. If someone with even a "high" skill set can't tell a table from a function, then I don't really want them in my database at all.Lots of folks say "pick a standard and stick with it". I'll say be very, very careful which standard you pick because you will be stuck with it. 😉 I'll also say that I'd never pick a standard that required any such object type identification whether it's call Hungarian Notation or not.
The advantage, the only one that I've given any credence to, is that you don't have to retrain people who are used to thinking that way.
For example, I work with a dev who has been using Hungarian notation like that in his code since the paleolithic, maybe longer. The cost of training him to stop using that is higher than the benefit gained. He's productive, gets lots of good code written, and since the whole dept is him and me, I can easily adapt to his habit with very little cost to me. Thus, the cost of keeping the prefixes is lower than the cost of eliminating them.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 15, 2009 at 8:42 am
When it comes to Stored Procedures, using Hungarian Prefixes can cause problems
SP prefix is kinda preserved for system stored procedures.
What I can't find out is whether the following is the same:
sp__SetValue
sp_SetValue
spSetvalue
Which of these will affect my processing speed when referencing my stored procedures?
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
July 15, 2009 at 8:53 am
sp_SetValue
is not recommended as it may take longer to process as SQL server will think that it could be a system stored procedure.
also you can not guarantee that future versions of SQL server will not have system stored procs with the same name..
July 15, 2009 at 9:08 am
So Steve what your saying is that if I use sp_Setvalue it will cause the problem but not when I use spSetvalue (without the underscore)?
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
July 15, 2009 at 9:12 am
roelofsleroux (7/15/2009)
So Steve what your saying is that if I use sp_Setvalue it will cause the problem but not when I use spSetvalue (without the underscore)?
yes pretty much, not sure how much of a performance hit it causes but it does cause some
July 15, 2009 at 9:15 am
I believe you are correct. It is the sp_ that causes SQL Server to look in master first, but, imo, you really don't need to prefix stored procedures and if you choose to why not use usp or up and then there is no confusion. I believe the sp prefix was used by microsoft to signify a system procedure not an stored procedure, just like xp is used for eXtended procedures.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 16, 2009 at 10:24 am
When I saw this question I had to add some information on how we do this in our shop. We do not generayy use prefixes for tables or views or functions, however to keep Stored procedures organized orderly and to eliminate the performance hit of searching for sp_ we use our own standard.
This standard goe back to the old mainframe Cobol coding days where the program was much easier to read if modules that did a function were grouped together by numbering them similarly.
We use a three character Id indication the application the database relates to followed by sp and a 5 digit number. TH e number groups the stored procedures tht perform similar functions i.e 80000 might be reports. The range is up to the individual. A stored procedure that calls another stored procedure will be similarly numbered so that we know these stored procedures are related.
An example of this is say your application is finance and you are writing a group of stored procedures to updaate the general ledger. The application would be abbreviated as Fin and the storecd procedures wouldbe named as follows.
Finsp_20000_ProcessGLTrans for the main stored procedure. It might call Finsp_20010_UpdateGlTotals and Finsp_20020_UpdateFinDetails. We would know fromthis scheme taht all three store procedures are part of the same function and they are also ordered together in the database.
This works wel for us and when the stored procedures need to be moved from development into production, the developer can just indicate the number and it is easier to indicate which stored procedures need to ne move.
We like the naming convention. Hope it helps others.
July 16, 2009 at 10:43 am
deewiley (7/16/2009)
When I saw this question I had to add some information on how we do this in our shop. We do not generayy use prefixes for tables or views or functions, however to keep Stored procedures organized orderly and to eliminate the performance hit of searching for sp_ we use our own standard.This standard goe back to the old mainframe Cobol coding days where the program was much easier to read if modules that did a function were grouped together by numbering them similarly.
We use a three character Id indication the application the database relates to followed by sp and a 5 digit number. TH e number groups the stored procedures tht perform similar functions i.e 80000 might be reports. The range is up to the individual. A stored procedure that calls another stored procedure will be similarly numbered so that we know these stored procedures are related.
An example of this is say your application is finance and you are writing a group of stored procedures to updaate the general ledger. The application would be abbreviated as Fin and the storecd procedures wouldbe named as follows.
Finsp_20000_ProcessGLTrans for the main stored procedure. It might call Finsp_20010_UpdateGlTotals and Finsp_20020_UpdateFinDetails. We would know fromthis scheme taht all three store procedures are part of the same function and they are also ordered together in the database.
This works wel for us and when the stored procedures need to be moved from development into production, the developer can just indicate the number and it is easier to indicate which stored procedures need to ne move.
We like the naming convention. Hope it helps others.
Seems a bit complicated as the ranges of numbers would have to be documented somewhere. Why not use a "prefix" like imp, rpt, fin, or whatever instead?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 16, 2009 at 10:54 am
We are a small shop and the numbers are just to keep the Stored procedures organized. They vary depending on how the developer(s) on the project wants to implement them. We have some general word of mouth guidelines since there are only 7 of us in the entire shop, but when you did into a project it groups the stored procedures together and it also provides a heirarchty for what calls what ( automatic implied dependencies).
As I said it works well for us, but in a bigger shop you probably would have to set some range standards and document them.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply