UpdateI did not get much feedback from this article, but one person wrote me and expressed a much different point of view than I had regarding abbreviations. I decided to update the article to clarify a few things as well as include an alternative viewpoint from another DBA.
Acronyms and shorthand are as much a part of IT as silicon, or so it seems. In keeping with this time honored tradition, I am proposing here that everyone extend this to include the T-SQL code that they write. C programmers are long familiar with using abbreviations in defining functions, procedures, and variables. Remember the famous Hungarian notation? It helped tremendously in tracking down problems with code by providing the programmer with information about the type of data contained in a variable, this type was expressed as an Abbreviation prefixed to the variable name. This can also be useful in database programming for a number of reasons, it helps to enforce standards, reduce typing, and prevent collisions with reserved words.
With a standard list of abbreviations for data elements, your colleagues can more easily understand what data elements your code is working with. Granted long names can do the same thing, but there are other reasons not to use long names. Standards are extremely important in many aspects of life, but in programming these days with distributed teams of workers, they can substantially reduce the amount of time spent working on someone else's code. My advice is to gather existing programmers together and create a list of abbreviations for the commonly used objects in your system. Then publish this list for all programmers to reference as well as an introductory document for new programmers. By agreeing to this list as a team, you will foster communication as well as help to get everyone to agree to a list.
I type fairly fast. While not a touch typist, I can type about 70 words a minute when I know what I want to type. If I type shorter words, then I can up this quite a bit. In programming, this means more lines of code per minute/hour/day spent at the keyboard. If I can reduce the amount of typing by using abbreviations, then less work for me. Or I get more done in less time and get a bigger bonus. Or (most importantly) I can spend more time with my family.
Look at this code:
update customerorders set shiptoaddress = '123 mystreet', shiptocity = 'The Big Apple', extrainstructions = 'drop off with landlady' where customerordersid = 999It has nice long names that are descriptive and convey some meaning to the reader. However, if I am constantly typing "customerorders" whenever I need to access this table, then I am going to get tired pretty quickly. Especially that we have a standard to use the table name + 'ID' for any identity fields. I once worked with a DBA that kept standards pretty well, but never used abbreviations. He setup a table that was heavily used called "TreasurySpecialist". For my non-touch typing fingers, this was a real pain to type. Especially since this became a heavily accessed table queried from dozens of stored procedures.
With abbreviations, I could easily rewrite the above code to look like the following:
update custord set shipaddr = '123 mystreet', shipcity = 'The Big Apple', xtrinstr = 'drop off with landlady' where custordid = 999While still not short, this is 30 characters less. Add that up over the course of the day and it's a lot less typing. While the meaning behind these abbreviations may not be obvious, it does not matter. We have a document with standard abbreviations that everyone refers to. Within a very short time, all developers instantly recognize the abbreviations and have no trouble coding with them.
In one database I inherited from a development team, there were two tables that constantly bothered me. One was called "Orders" and the other was "OrderItem". Apart from not using abbreviations, the part that I had to think about (and was bothered by) everytime I referenced them was whether "order" was singular or plural. I knew why this was the case; the developer who created the tables wanted them to be "order" and "orderitem", but "order" is a reserved word in SQL Server (and probably more RDBMSes). So he created this inconsistency. I also got burned on a v6.5 to v7.0 database upgrade. "Percent" is not reserved in v6.5, but is in v7.0. Lucky for me there were about ten tables that used this column in the v6.5 database and were queried from a VB app on many desktops across the country (of course the queries were hardcoded). Can you imagine the fun I had?
Both of these situations could easily be avoided by using abbreviations. If "order" were changed to "ord" and "percent" to "prcnt", then no problems would have resulted and development would not have been delayed. In fact, the time to setup abbreviations and learn them would probably be offset by the reduced typing and mistakes that could result. In addition, you would not be likely to collide with new keywords that may be created in SQL Server 2001.
One note here, with SQL Server 7, you can enclose your variable and object names in brackets  and embed white space as well as use reserved words. I personally do not like this approach and hate embedded white spaces (I think they create more typos and confusion than they are worth) but this is a perfectly valid approach in the SQL Server world. I cannot speak as to whether other RDBMS systems support this.
This is a results oriented arena, so I would be remiss if I did not provide examples of abbreviations as well as a means to maintain them. The example is an Excel 2000 spreadsheet, so apologies to those of you without Excel 2000. Here it is and it includes a macro to re-sort the data. As for the maintenance, what I normally do is setup a share on the network that all developers have access to and put the spreadsheet in there along with other development documents (standards, practices, etc.). Then each developer can check the spreadsheet whenever they are unsure of an abbreviation.
For new abbreviations, you have two choices, both of which have worked well in the past. First, you can appoint someone to be the keeper of this document and everyone else has to "request" new abbreviations from this person. The job can be rotated, but this keeps the system ordered. Alternatively, you can have a new abbreviation sent around in email and "voted" on by the group. If everyone agrees, then the requester is responsible for adding it and resporting the sheet.
I do not intend this as a universal list of abbreviations. In fact, you should save the spreadsheet and then delete all the data in it and add your own abbreviations that are appropriate to your company. In my last job, we used Pro for Product, though not consistently. In my current company, Prod was chosen as a team. Either one can work, just be consistent and enforce the standard.
Abbreviations good, no abbreviations bad! Well, it may not be quite that simple, but it's close. Over time I have found this to be a real time saver with not much work involved. It usually takes more time to explain the rational to someone and then have them nod and agree than it does to implement. I feel and have found that having abbreviations as the standard usually helps enforce this and less programmers take shortcuts and create their own abbreviations in their section of code. It is rare I find a programmer that wants to type more than they have to.
I hope this helps and as always, eelcome any feedback from you.
An Alternative Viewpoint
I received this from Jim Sally ( I removed the intro and conlusions parapgraphs which were not relevant)
First off, yes acronyms and shorthand have had a long time tradition in software development, but... this is the year 2000, memory is a dime a dozen (or so to speak), and in your specific instances, SQL 7 and above have extended the namespace size of its identifiers beyond the 32 character limitations of earlier versions. This wasn't done just for the hell of it, but as a thought out decision to let database developers create VERY self-descriptive names for database objects.
In general, software development has refined itself to the point of being able to call some instances of itself 'software engineering'. The engineering part of it is important. And part of some of my most favorite parts of that deal with writing software that is NOT abbreviated, but very specifically spelled out. When I'm working with new folks joining my company, I don't care if they take twice as long to type out stuff using my naming schemes, because once they are done, it is very obvious what they typed since it is spelled out in clear english.
Take your example as a perfect example of hard to read stuff if I were to come along and pick up your code after you left.
extrainstructions => xtrinstr
At first glance, even after the 10+ years of software development experience that I've had, 'xtrinstr' means nothing to me, where as 'ExtraInstructions' (note the proper capitalization too) has a very specific and clear meaning.
I also think that your use of Hungarian notation as an example of why people should abbreviate is just wrong. First off, Hungarian notation was not an "abbreviating notation", but a prefixing notation for type information. It was useful information to have as a programmer while coding, but not necessary for following the logic in the code, which is why we name identifiers using english words rather than 'value1', value2', value3', etc.
I can see if you were going to suggest using Hungarian notation in naming your fields, but you didn't.
Bottom line, your article is yet another reason that young and inexperienced developers turn out half-assed applications that are hard to maintain and understand. And all in the name of saving time typing.Jim Sally
I agree with much of what Mr Sally writes and apologize if I misled anyone with the analogy to Hungarian Notation (which is an abbreviation for a type). But I also think that I have a valid method of helping software development. In my experience, using abbreviations is productive and speeds development when used properly. The shorthand notation I am proposing has been used widely in written communication and I think it can be applied here.
I do believe that the namespaces were extended precisely as Mr Sally to allow for more descriptive variable and object names. My time as a senior DBA, however, is valuable and saving me time is more efficient than saving a junior DBA time. I would also argue that the meaning of variables, even long, spelled- out descriptive names is not going to help junior programmers who are not familiar with the company's lexicon (which is also where most variable names come from). Is Customer really better than Cust? What does this mean in your company? Is it the same as User or Usr? Maybe, maybe not.
The abbreviations I suggest are not intended to be universal, nor are they suited for every environment. I hope that you publish a standard set of abbreviations for your company as well as a set of standards for naming, coding, etc. that is given to every new programmer that joins the company. One of the standards that I learned early on was to use i, j, and k as counter variables. I still do this and let my programmers know that this is a standard that they will find in my code, rather than using counter or some other name. I expect and enure they do the same. There is not substitute for a good data dictionary and there is also no substitute for a well written, clear, and concise set of programming standards as well.
I hope that I did not lead anyone to believe that you should remove all the vowels from your coding and expect people to understand the code. Indeed, some of my "abbreviations" are in fact the entire word ( email, fax, etc.). You should develop a set of standards as a team of programmers and then publish them for everyone to use. If you do this, then all existing programmers will easily adapt to the situation and all new programmers can be given a set of documentation that will familiarize them with how your company's code is written.
Let me emphasize that Mr. Sally makes a number of perfectly valid (and accurate) points. I appreciate his feedback and agree to disagree with him. To me, this is more a stylistic arguement about how to write code than a right or wrong. If I were to go to work for Mr. Sally, I would argue my case for abbreviations, but if the standards he has set in place are to use long names, then I would code according to those standards at his company.Steve Jones October 2000