Keeping Unique Aliases

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719107

    Comments posted to this topic are about the item Keeping Unique Aliases

  • Jeff Moden

    SSC Guru

    Points: 996449

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    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.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Forum FAQ

  • David.Poole

    SSC Guru

    Points: 75348

    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

     

  • Japie Botma

    SSCrazy

    Points: 2940

    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

  • call.copse

    SSCoach

    Points: 17152

    Japie Botma wrote:

    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.

  • Japie Botma

    SSCrazy

    Points: 2940

    LOL.  (Not random, alphabetical)  🙂

    5ilverFox
    Consulting DBA / Developer
    South Africa

  • Neil Burton

    SSC-Insane

    Points: 22179

    Japie Botma wrote:

    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!


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • Jo Pattyn

    SSC-Dedicated

    Points: 31393

    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

  • gvoshol 73146

    Hall of Fame

    Points: 3175

    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.....

    • This reply was modified 1 month ago by  gvoshol 73146. Reason: Comments on single vs multi-letter aliases
  • RonKyle

    SSC-Dedicated

    Points: 31482

    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.

  • roger.plowman

    SSChampion

    Points: 10236

    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.

     

     

  • Tom_Hogan

    Hall of Fame

    Points: 3565

    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.).

  • Neil Burton

    SSC-Insane

    Points: 22179

    roger.plowman wrote:

    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.


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719107

    Japie Botma wrote:

    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.

  • Jeff Moden

    SSC Guru

    Points: 996449

    Tom_Hogan wrote:

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    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.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Forum FAQ

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply