April 28, 2020 at 12:00 am
Comments posted to this topic are about the item Keeping Unique Aliases
April 28, 2020 at 4:33 am
Totally agreed about the usefulness of standards but I have to say that bad standards are as bad and sometimes worsen than no standards.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 28, 2020 at 6:06 am
100% with you on consistency. Having mechanical tests for consistency helps. People so argue with machines they argue with other people.
Understanding the "WHY" of a standard is as important as the standard itself.
Peer reviewing code goes much faster if there are consistent standards. I find I'm focusing more on the code functionality rather than getting distracted by its layout.
I just wish people wouldn't use Id as the primary key column name
April 28, 2020 at 7:24 am
I agree, but I personally don't like multi character aliases. For me, they clutter the code and make it less readable. I like to name them a,b,c,d... and that makes finding the table name easier too, because everybody can read a dictionary. Way too difficult for me to remember that p is for Products and not Postal Codes.
Did I just put a cat among the pigeons?
5ilverFox
Consulting DBA / Developer
South Africa
April 28, 2020 at 8:21 am
I agree, but I personally don't like multi character aliases. For me, they clutter the code and make it less readable. I like to name them a,b,c,d... and that makes finding the table name easier too, because everybody can read a dictionary. Way too difficult for me to remember that p is for Products and not Postal Codes.
Did I just put a cat among the pigeons?
Lol not exactly a cat among the pigeons but certainly bring back awful, horrible memories, of maintaining a database where someone had used this 'method'. After trying to debug issues while scrolling up and down hundreds of lines constantly to find out what corresponded to a, b etc etc I wished an unpleasant death upon the perpetrator many times over. You forget what you were thinking of when you have to keep doing that and it takes ages to get anywhere.
If you are happy to be regarded in this extremely negative light please feel free to continue. I'd suggest a system where the alias in some way relates to the table, makes everyone's life that comes after, much easier. Products can be p, or prod, PostalCodes can be pc or pcodes, but a random correlation? This only makes sense if you hate your cow-orkers and want them to hate you. Honestly.
April 28, 2020 at 8:27 am
LOL. (Not random, alphabetical) 🙂
5ilverFox
Consulting DBA / Developer
South Africa
April 28, 2020 at 9:19 am
I agree, but I personally don't like multi character aliases. For me, they clutter the code and make it less readable. I like to name them a,b,c,d... and that makes finding the table name easier too, because everybody can read a dictionary. Way too difficult for me to remember that p is for Products and not Postal Codes.
Did I just put a cat among the pigeons?
I think this is an area where setting a standard would be more difficult than usual. The main thing is clarity of code and I disagree that a multi-character aliases clutter it (as a rule). If a multi-character alias makes things clearer then use one, if a single character will do then that's good too.
I use single character aliases if the name of the table warrants it i.e. c for Contact, s for Service etc. If I'm referencing the FilteredContact view or the OrderService table, I'll use fc and os respectively. If I have to refer to table more than once in a query, such as a date dimension for event dates and processed dates, I'll use event and processed etc.
The two things I would avoid are using the whole table name, unless it's particularly short, and using single characters alphabetically. If works for you Japie then more power to your elbow but it gives me cold shivers!
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
April 28, 2020 at 10:11 am
I personally dislike single letter aliases, usually too short to have a representative meaning. With a as (), b as (), c as (), d as () . Select ... from a join c where not in (select ... c join d). Almost selecting acdc
April 28, 2020 at 11:13 am
Alphabetical by what? Table name, or order in the joins?
I suspect the latter. Which means in one query you might have
Order as a
Join OrderItem as b
And in another it might be
OrderLine as a
Join Order as b
I'd prefer
Order as o
Join OrderLine as ol
-- or
OrderLine as ol
Join Order as o
Order is always o and OrderLine is always ol.
As far as short aliases (1 or 2 letters) I prefer them to keep the code compact.
In any organization there are certain key tables that are used over and over again in queries and views. I work in health insurance, so we have tables like Member, Provider, Claims, etc. In my code, these get the single letter aliases. Other tables being related would get longer aliases - but again I try to use the same for each one. That does run into complications from time to time - for example, is cd used for ClaimDiagnosis or ClaimDetailServiceLine? Sometimes the latter is cd, sometimes sl. I have a hard time following my own conventions.....
April 28, 2020 at 11:50 am
Completely agree with the consistency argument. I've seen inconsistency within even the same stored procedure written by a single person. I also like the single letter table. I've rarely run into a case where taking the first letter of each word in the object name doesn't yield a unique result for a given query. WorkOrder is wo. WorkOrderDetail is wod. So a join on wo.WorkOrderID = wod.WorkOrderID seems very clear to me.
I cannot stand the a, b, c, d convention. I haven't seen that in a long time. We had a lot of consultants, all of whom had been educated in Indian universities, who used this. I assumed this was the way they had been taught and become accustomed to it. I was always have to look up to remind myself what table that was, however. It may be a cross-cultural preference.
April 28, 2020 at 12:18 pm
I prefer single-character-per-word aliases myself. But that brings up the subject of collisions. Some queries have insane joined table counts and when you have [ATM] and [Audit] in the same query you have to get--creative.
Standards are good, no question. As a lone wolf developer I have the luxury of creating my own, but that doesn't mean I don't have any.
Generally, I find that using the first letter of the table name (or first letter of each word) works really well. You avoid the confusion of arbitrary aliases and still retain some mnemonic clues while minimizing the size and clutter of longer alias names.
April 28, 2020 at 1:28 pm
Totally agreed about the usefulness of standards but I have to say that bad standards are as bad and sometimes worsen than no standards.
Not to detail the thread, but I'm dying to know some of the bad standards you've seen.
I prefer single letter per word aliases. I've been lucky enough to establish the standards at most places I've worked but haven't thought about one for table alias (except to not do a, b, c, etc.).
April 28, 2020 at 1:29 pm
I prefer single-character-per-word aliases myself. But that brings up the subject of collisions. Some queries have insane joined table counts and when you have [ATM] and [Audit] in the same query you have to get--creative.
Standards are good, no question. As a lone wolf developer I have the luxury of creating my own, but that doesn't mean I don't have any.
Generally, I find that using the first letter of the table name (or first letter of each word) works really well. You avoid the confusion of arbitrary aliases and still retain some mnemonic clues while minimizing the size and clutter of longer alias names.
I'd actually say that ATM and Audit tables had short enough names to either use the full name as the alias or not alias them (which I know has its own drawbacks). In general I do like the single letter per word approach but as I said, it's whatever makes things clearest.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
April 28, 2020 at 2:08 pm
I agree, but I personally don't like multi character aliases. For me, they clutter the code and make it less readable. I like to name them a,b,c,d... and that makes finding the table name easier too, because everybody can read a dictionary. Way too difficult for me to remember that p is for Products and not Postal Codes.
Did I just put a cat among the pigeons?
I find a, b, c, d less useful since this means I need to hunt through the FROM to remember. While I do line single character ones (holdover from 8 and 16bit Unix), I don't mind p for product, pc for productcategory, psc for productsubcategory, etc.
April 28, 2020 at 2:40 pm
Jeff Moden wrote:Totally agreed about the usefulness of standards but I have to say that bad standards are as bad and sometimes worsen than no standards.
Not to detail the thread, but I'm dying to know some of the bad standards you've seen.
I prefer single letter per word aliases. I've been lucky enough to establish the standards at most places I've worked but haven't thought about one for table alias (except to not do a, b, c, etc.).
Single letter aliases that don't mean anything would be one of those "bad" standards for me because, like Steve said, you have to memorize everything in the FROM clause especially on larger queries with a shedload of joins. Sure, you can also split the screen to help things out but we actually have some pretty standardized abbreviations for the more commonly used tables and they normally don't need to exceed 3 character. For example, we always use "il" for the InuranceLines table and "ilc" for InsuranceLinesCollateral as a couple of examples.
I also try to get people to name their CTEs after what they do or contain. My boss has the habit of naming all his CTEs "Dn" where the "n" is a single digit or two digit number. I asked him to follow the rather loose naming convention I suggested and he gave me this long assed story about how well it worked for him. I told him he's the boss and so it's his shop and he can do what he wants but, someday, he's going to wish he followed my suggested standard.
The very next day, he PM'd me saying something to the effect of how this crusty ol' bastard was right because his naming convention confused the hell out of him on some code he wrote just 6 months earlier. Seems like he had to actually read and figure out what each CTE was doing and then find a way to keep it all sorted in his head to make changes. He also doesn't comment code so big double whammy.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply