Long names vs short names (or aliases)
9June 15, 2021 by Kenneth Fisher
I remember when I was first learning to code there was always the argument of how long a name should be. For example the table msdb.dbo.log_shipping_monitor_history_detail. That particular table name is 35 characters long, and it’s not the longest table name I’ve ever seen. Can you imagine typing that a dozen times in a query? How much easier would it be if the name was significantly shorter?
On the other hand, can you imagine if it was just named lsmhd? Or just tab001? And yes, I’ve seen object names similar to both. Much easier to type, knowing what it is on the other hand, not so easy.
Things like aliases and synonyms have made this less of an issue but it’s still an important consideration. Long and easy to understand, or short and easy to type. As I’m sure is obvious by now, I thought I’d throw in my two cents. And to be fair, this is entirely my opinion on this, you are welcome to have your own.
- Object names should be as long as necessary to be completely readable and understandable. That means if your table name needs to be 100 or even 200 characters long for someone to be able to walk up and say “Hey, That’s where I can find xyz!” then that’s what you do.
- Within a query every object should be qualified. i.e. at least a two part name depending on the type of object and if it’s in the current database. This means that column names should include the table, table names should include at least the schema, possibly the database etc.
- Aliases
- Should be used if the original name is longer than you feel like typing throughout the rest of the query.
- Don’t have to be readable. Short is more important here. So lsmhd would actually work for an alias for the table above.
- Should be used in such a way that in the end your query is more readable, rather than less.
That last line is everything. Your naming conventions, for all types of objects and aliases, should make things easier to understand. For every time you type your code out, you (or someone else) will be reading it dozens of times (at least). Guess which I think is more important.
SQL Studies 
Ken, agreed, however I believe Microsoft will limit you to 128 characters, at least that was the size of a sysname object last I checked. 🙂
Excellent point! I was being somewhat hyperbolic there, because let’s face it, even at 100 characters you are starting to go backwards at readability for the most part.
My preferred usage is: «grouping schema name».«one/two word table name»_«suffix». the suffix is unique to the table and is short as is practical. All primary keys, foreign keys and so on use the suffix.
So, a personnel table would be something along the lines of hr.employees_em with ID_EM (or PK_EM, EMPK, EMID etc ) as the primary key (if there it is a simple, one-column primary key) and FK_EM (or EMFK etc.) as the foreign key name in referencing tables. And if you are of the opinion that table names should be singular, please read it as hr.employee_em. Consistency is key.
I like the explicit suffix as it serves to standardise the aliases used in handwritten scripts. It makes proofing colleagues’ scripts much easier, I have a good idea what the column EMFK means and does and finally, with a tool like SQL Prompt from Redgate, I can generate snippets very easily and load them up into the tool, which makes script generation much faster.
Sounds great. Like you said, consistency is key. That and readability. If someone new can walk up and read your code (comments at all) and understand it then you’re golden!
There are several standards for naming things in SQL. You can read ISO 11079 start on this, then go to the metadata committee documents.. Since tables model sets of entities, they should be collective or plural nouns not singular (well, unless you really do just have one of them). For example, “Employee” is bad; “Employees” is better; “Personnel” is best. “Tree” is bad; “Trees” is better; “Forest” is best.
The general format for a data element name is [_]_. Thanks to the Unicode standards, you are guaranteed that every printable character set and every ANSI/ISO programming language. will allow simple Latin letters, digits, and a limited set of punctuation marks. Your data element name should be usable in any piece of software and not just SQL. ISO happens to like the underscore since it is measurably easier to read (after a few centuries of reading text written on lined paper, our eyes and brains are very well-adjusted). Thus, we have names like “tree_diameter”, “tree_species”, “tree_id”, etc. occasionally, the may have to be a little longer than that, such as “tree_species_group”, but not actually all that often.
Never use an underscore or other punctuation to start a name. Never embed a pair of underscores in a name. Always remember that another human being has to read your code later, so pick good clear names and don’t get cute. Many decades ago I worked for a state agency that had a programmer who would pick a theme for his COBOL. In those days, IBM would like to use up to 19 characters in a COBOL name. One of his programs was based on geography, so there were statements like “GO TO Afghanistan” or or “PERFORM Brazil”; the worst one was using the names of flowers. Does anyone remember how to spell “chrysanthemum”?
The worst I remember was a piece of accounting software where the names were TAB001, TAB002 etc and COL001, COL002 etc. We had a binder that we had to use to tell us what was in each of the columns. When developers needed a new column they would pick one in that table that they were pretty sure didn’t have any data in it. As you can imagine it was a horrible mess.
Years ago, we had a programmer who wrote something in Fortran. But he didn’t really know Fortran, he knew one of the earlier forms of Basic. So every one of his variables was 1 or 2 characters. We had T, T1, T2, TT, etc. My coworker decided it would be easier to rewrite it from scratch than try to analyze it to see what needed to be changed for some (relatively minor) new requirements.
That said, when I use aliases in SQL queries, I usually give our common tables a single or sometimes double letter. We have tables like Member, Claims, ClaimDetail that are used everywhere. So I always use the same short alias for those tables: m, c, etc. Other tables I use 3 or 4 characters related to the table name. That works pretty well until you get something like lcd, lcm, lcr, lcrx in the same query, because it’s joining a bunch of similarly named tables. Generally the only time I use a number in an alias is when I’m doing a self join, for example Member as m1 JOIN Member as m2 ON {conditions}.
For Joe C, what do you do if your database about trees has nothing to do with a forest? For example, it might be a nursery that grows a lot of trees that they need to track. Then isn’t “Trees” the more appropriate name? They might also grow bushes, perennials, annuals, vegetables, etc that have their own tables because they have distinct attributes.
>> what do you do if your database about trees has nothing to do with a forest? For example, it might be a nursery that grows a lot of trees that they need to track. Then isn’t “Trees” the more appropriate name? <<
I got the example of "trees" from an example used in the ISO 11179 documents. If your table is not modeling a forest, it needs a more appropriate collective or plural noun. When you're naming things you need to start from the highest conceptual level and then moved to the particulars.
In your example of a nursery, I would guess you would prefer something like "Inventory", whose elements can be trees, bushes, those funny-looking lawn gnomes, pots, planters, etc. They would be identified by UPC or GTIN barcodes. Things in an inventory will each have a stocking date, cost and pricing information, a removal date (by sales, by spoilage, by shrinkage), etc. These barcodes would then be used in appropriate detail tables.
I tend to follow using readable table names and keeping the schema around, but I prefer short aliases – a single capital letter almost always. And I am a bit inconsistent with using alias.columnname in select statements – use them more reliably in ON statements. and don’t use aliases at all when selecting from a single table or view.