July 7, 2009 at 1:44 am
There are a lot of opinions regarding standards or general preference on the subject of database object naming conventions.
I'm used to a environment where object have prefixes like tbl, vw, sp and fn.
tblEmployee
vwGetActiveEmployee
spStoreEmployee
fnGetEmployeeList
Although i read that that a sp_ prefrix can result in a performance penalty seeing as SQL server uses this in the Master Database as a standard prefix for Stored Procedures. This means that when you want to use your own stored procdure with such a prefix, SQL will try and locate it in the Master Database first and then try your database. Producing a performance slack.
My question:
Are there a standard or would it be wrong to use prefixes?
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
July 7, 2009 at 8:26 am
I don't use prefixes on tables and stored procedures. I do tend to use prefixes on views (vw) and functions (fn) to differentiate them from tables and stored procedures. You'll get as many opinions on this as there are people in the forums. In my opinion the important thing is that the organization have a standard and stick to it.
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 7, 2009 at 9:28 am
I don't use any prefexies in my production databases. The reason is that a table could be changed to a view or vice versa and having a prefix would just complicate things. Add synonyms in on top of that.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 7, 2009 at 9:36 am
I don't use prefixes by choice. Current employer requires them, so I'm using them, but I dislike it.
I don't find that it adds anything useful. It's not like I can't easily find out what object type something in a From clause is.
I do find that it creates problems. As Jeff mentioned, what if you want to change from a table to a view? If you name the view the same as the old table, you don't have to change any code that accesses it. If you have to rename it, to change a "tbl" prefix to "vw", then you have to change every piece of code that accesses it. That doesn't really apply to UDFs, because you have to at least have parentheses after them, which you wouldn't have on tables/views, but at the same time, the parentheses also eliminate any value from having a "udf" or "fn" prefix on it, since you can visually determine object-type by that.
So, I don't see a benefit to it, and do see drawbacks.
However, I don't consider it important enough to fight over. Go with what you're comfortable with. Set a standard and stick to it. That's more important than the specifics of the standard, in this case.
- 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 7, 2009 at 10:59 am
I do not use prefixes for object names for the reasons stated above, also I use SQL prompt and I find that the use of prefixes does not work well with the auto-completion of names as every table will have the same few starting characters, and this also annoys our developers as the Intellisense in VS.Net has the same issues..
July 8, 2009 at 8:31 am
Hey Guys 🙂
So far the overall decision is to use Hungarian Prefixes. Even though i realize the risks involved, the decision is based on understandable reasons:
- Large development team with different expertise level members will be working on the database and needs to distinguish between different object.
- Most find it more readable and different object stand out from queries.
- Chances of Tables->Views conversation are extremely low.
Thanks for your advice though.
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
July 8, 2009 at 8:34 am
Like I said, pick a standard and stick to it. In this case, that matters more than what the specifics of the standard are. The advantages/disadvantages to each are minor.
- 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 8, 2009 at 8:42 am
Thanks GSquared
I found with previous projects that if you a strict standard that you stick with it makes the occasional adjustments so much easier.
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
July 8, 2009 at 2:42 pm
roelofsleroux (7/8/2009)
- Most find it more readable and different object stand out from queries.
I'm sure you can produce a white paper on the multitude of opinion polls that substantiates that statement, right? 😉
And for large development teams having a plethora of skill levels, they should have no reason to know the difference between a table and view. If they don't know how to find out if it's a table or a view, perhaps they shouldn't actually be writing SQL.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 8, 2009 at 2:46 pm
GSquared (7/8/2009)
Like I said, pick a standard and stick to it. In this case, that matters more than what the specifics of the standard are. The advantages/disadvantages to each are minor.
BWAA-HAA!!! You left out a word....
Like I said, pick an intelligent standard and stick to it.
Hungarian notation in SQL server just doesn't fit that bill for me especially since all tables, regardless of how they are named, all fall under the table icon in the object browser. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
July 8, 2009 at 2:56 pm
Jeff Moden (7/8/2009)
GSquared (7/8/2009)
Like I said, pick a standard and stick to it. In this case, that matters more than what the specifics of the standard are. The advantages/disadvantages to each are minor.BWAA-HAA!!! You left out a word....
Like I said, pick an intelligent standard and stick to it.
Hungarian notation in SQL server just doesn't fit that bill for me especially since all tables, regardless of how they are named, all fall under the table icon in the object browser. 😀
I agree that it's not necessary, but I've had to work with both, and I don't see an overwhelming advantage to either one.
Cursors vs set-based, there's an overwhelming difference. Temp tables vs table variables, there are overwhelming advantages depending on exact needs. Lots of IF statements calling lots of different selects vs calling sub-procs, there are overwhelming advantages in the second. Splitting a string with a loop vs a Numbers table, overwhelming differences.
Using "tblMyTable" vs "MyTable", there are minor advantages. Very minor.
So, I have to disagree with the "intelligent" point, at its face value.
- 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 8, 2009 at 2:56 pm
-- Bugs on the page creating duplicate posts
- 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 8, 2009 at 7:18 pm
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.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 8, 2009 at 7:46 pm
I'm always amazed by how passionate people are about different naming/case standards.
Personally I tend to use "prefixed Camel notation" (eg. tblMyTable or fnMyFunction) for my personal development activities, but I'm not committed enough to try to force that. As I commonly deal with databases from a plethora of internal development teams and third-parties the only thing I'm convinced of is that you have to be flexible.
One thing I have observed though is that the absence of any discernible naming standard is a very frequent indicator of a poorly designed database/app, approaching 100%. If someone has taken the time or has the experience to decide on a naming standard then they've generally expended some thought regarding the structure of the database and the efficiency of the code.
I groan every time I have to look into the performance of a system i haven't seen before when I see there's no consistent naming standard. In fact, the more naming standards that have been used in a database (eg. tables called tbl_MyTable, tbl_My_Table, MyTable, MyTbl, mytbl, my_tbl, etc.) the worse it generally is, the more duplicated data it contains and the less referential integrity ("oh, the app looks after that").
Abbreviations fall into the same category. We've one rubbish system that has some tables suffixed with "LINE", some with "LIN" and some with "LN".
So my advice is to not get hung up too much on any given standard because you're almost certainly going to have to work with several over the course of your career, but if there's one there stick to it. If there isn't one, implement one as early as possible in the project and review that the code/objects are using it consistently when doing your code/object review (you do do that, don't you?:-D).
July 8, 2009 at 7:47 pm
I'm always amazed by how passionate people are about different naming/case standards.
Personally I tend to use "prefixed Camel notation" (eg. tblMyTable or fnMyFunction) for my personal development activities, but I'm not committed enough to try to force that. As I commonly deal with databases from a plethora of internal development teams and third-parties the only thing I'm convinced of is that you have to be flexible.
One thing I have observed though is that the absence of any discernible naming standard is a very frequent indicator of a poorly designed database/app, approaching 100%. If someone has taken the time or has the experience to decide on a naming standard then they've generally expended some thought regarding the structure of the database and the efficiency of the code.
I groan every time I have to look into the performance of a system i haven't seen before when I see there's no consistent naming standard. In fact, the more naming standards that have been used in a database (eg. tables called tbl_MyTable, tbl_My_Table, MyTable, MyTbl, mytbl, my_tbl, etc.) the worse it generally is, the more duplicated data it contains and the less referential integrity ("oh, the app looks after that").
Abbreviations fall into the same category. We've one rubbish system that has some tables suffixed with "LINE", some with "LIN" and some with "LN".
So my advice is to not get hung up too much on any given standard because you're almost certainly going to have to work with several over the course of your career, but if there's one there stick to it. If there isn't one, implement one as early as possible in the project and review that the code/objects are using it consistently when doing your code/object review (you do do that, don't you?:-D).
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply