Karl Kieninger (12/5/2012)
1.Use of Hungarian (prefix or suffix) for object names ("tibbling")
2.Pluralizing of tables
3.Abbreviating and standard abbreviations ("abrvtng")
4.Use or avoidance of [Escaping]
5.Indication word boundaries (pascal, camel, _, etc.)
8.Association/join/many-to-many table names
I know at least one person who will take grand exception to my naming standards and use of identity columns. Others will take exception to my use of casing. That, notwithstanding, here's an overview of what I use based on your numbered questions. Even those good folks that take such exceptions have complimented the code I write as being extremely easy to read. I strongly enforce these conventions in code reviews at work. The exception to that is I don't enforce my flavor of casing on MS or 3rd party object/column names because things like Intellisense would really slow people down if they had to follow my casing conventions there.
Again, these are my particular conventions and I'm not even suggesting that they will meet anyone's approval. Also, when I'm doing work for some shop other than my own, I will follow their conventions even if I think they're ridiculous (like the one shop that had the convention that every comma must be on it's own line, everything had to be in lower case (with underscores, of course), and some insane abbreviation requirements).
1. I almost never use Hungarian notation. If I need to change a table to a view (for example) to keep underlying table change from necessitating a change in the GUI, I rename the table to something else and make a view with the original name. With Hungarian notation, the would mean I would have a view that had the prefix of "tbl". I've also had the need to change views to tables. The same goes for column names. If you change the data-type from int to bigint (for example), then you're either stuck with the wrong name everywhere or you need to change it everywhere. I will use a "cte" prefix for CTEs and I will prefix parameter variables with p, pi, po, or pio depending on what and how they are used.
2. I almost never pluralize table names. I'll have a table named "Company". I can't see having one called "Companies" especially if you use IDENTITY columns and follow the naming convention of tablename
ID for such columns. To wit, I name the table after what a single row contains information about.
3. I almost never abbreviate because too many people have different standards and too many words end up having the same abbreviation. I'll use "N" in a Tally table but I never use "num", "numb", "nmbr", or "no" (for example. There are 128 characters available for most object names so I use intelligent naming and usually no abbreviations. With things like Intellisense and reasonable alias names, there's really no need for abbreviations anymore. Well, unless you're limited to 30 characters like in Oracle. :-D An obvious exception is "ID".
4. I almost always try to avoid bracketed/quoted names. It's butt ugly and causes visual clutter, IMHO. There are exceptions especially when using non-plural names such as a table called "User". I think MS made some pretty big mistakes with their object naming especially when it comes to "Object_ID". You should never name a column the same as a function, IMHO.
5. For objects I create and call it what you will, I use mostly "InitialCaps" (first character of each word is capitalized)with no spaces or underscores for names ("ID" is always fully capitalized for me) and I only use underscores for system related prefixes (which is also another exception for abbreviations).
6. I use all upper case for keywords including functions and data-types. I use lower case for schema names and table aliases. For object names and variables, I'll use the "InitialCaps" I previously spoke of even if the object is MS or 3rd party provided for the sake of readability. Obviously, I need to preserve any underscores someone else uses but I really don't worry about case sensitive servers because I won't allow them in my shop. I occasionally have the need to have a case sensitive column but I'd likely quit a job that required me to make a case sensitive server. That's one of the big reasons I don't work with Oracle anymore. It defaults to case sensitive and I can't stand it. I do try to write case sensitive code on forums just to play nice for the people who may have case sensitive servers. I also find it odd that it would appear that MS has no such casing standard even on objects in the same database.
7. I use PK_tablename (tried to follow the MS convention to make life easier even though underscores are present). Same goes for IX and FK except that either column names are used or, for such objects with a lot of column names, I'll throw in the word "CompositeXX" ("XX" is a 2 digit number) to replace all of the column names. For FKs, I generally follow the MS standard and will repair any abbreviations/truncations.
8. As an exception to the "no underscores" rule that I usually use, I name bridge tables after the two tables the bridge and may or may not use an underscore to separate them depending on a choice by whomever I'm working for. I don't use both standards on any given system. I settle on one or the other and enforce it.
You should also have standards for indenting and commenting. My standard for commenting is that every Insert, Update, Delete, and Select (even correlated sub-queries and derived tables) must have a short comment explaining the "why" each should exist in the code. For my personal code, if you were to remove all of the code, you could draw a functional flowchart from the comments. Comments are the one thing I won't compromise on when working in another shop and I let them know that up front. They can remove them (there are tools that easily do such a thing) if they want but what I check into SVN (etc) will always have such comments. Most shops are highly appreciative of the comments, though.
I also strongly enforce the use of 2 part naming conventions and the general avoidance of 3 and 4 part naming except where absolutely necessary (in a Synonym, for example).
I have other conventions like avoiding exclusionary outer joins in favor of NOT IN or NOT EXISTING for performance reasons and avoiding the use of Table Variables to make troubleshooting easier but we'll leave most of those alone here.
is pronounced ree-bar and is a Modenism for R
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair
How to post code problemsHow to post performance problemsForum FAQs