Enhancing the readability of your code: Table aliasing in sql

  • Jan Van der Eecken (5/7/2009)[hr

    Samuel, no, I don't put ANDs and ORs at the end of the line. They go where they are most easily visible and comprehensible for the reader who comes after me, i.e. at the beginning of a new line of code AND properly indented.

    Re Grammatically looking wrong, no, it is not. There are no rules in any natural language I know of that impose a rule as to whether a comma must be at the end of a line or at the beginning. If we are talking grammar, then only the rules as to where a comma must be placed inline to make syntactical or sementical sense are defined. It's only some older computer languages that imposed rules that forced one to put certain statements at certain offsets from the begiinning of a new line. Darn, I hated those.

    Whoah! calm down

    I said it looked gramatically wrong, not it is grammatically wrong

    Leading commas, for me are just not pleasing to the eye, it "looks wrong"

    Anyway, I'm sure we can argue about this till we're blue in the face and not come to an agreement about it 🙂

  • Samuel Vella (5/8/2009)


    Jan Van der Eecken (5/7/2009)[hr

    Samuel, no, I don't put ANDs and ORs at the end of the line. They go where they are most easily visible and comprehensible for the reader who comes after me, i.e. at the beginning of a new line of code AND properly indented.

    Re Grammatically looking wrong, no, it is not. There are no rules in any natural language I know of that impose a rule as to whether a comma must be at the end of a line or at the beginning. If we are talking grammar, then only the rules as to where a comma must be placed inline to make syntactical or sementical sense are defined. It's only some older computer languages that imposed rules that forced one to put certain statements at certain offsets from the begiinning of a new line. Darn, I hated those.

    Whoah! calm down

    I said it looked gramatically wrong, not it is grammatically wrong

    Leading commas, for me are just not pleasing to the eye, it "looks wrong"

    Anyway, I'm sure we can argue about this till we're blue in the face and not come to an agreement about it 🙂

    Apologies, Samuel, I must have misunderstood you. I wasn't getting upset by the way :-). And you are right, one can argue about style for ever and ever. The lowdown is, if you are working as an individual, do whatever pleases your eye, but make sure the next person working on your code (if ever) can actually read it without having to spend hours and hours reformatting before getting any real work done. If you work for a company and they do have a coding standard, either stick to it, or try to convince them to change (get your arguments worked out well first), or if that doesn't help, and you really can't live with those standards, you have only one choice: get yourself another job.

    --------------------------------------------------------------------------
    A little knowledge is a dangerous thing (Alexander Pope)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

  • rodgear (5/7/2009)


    I've solved the comma argument for you all. I've taken them out all together. 😀

    The best part is that you only need a keyboard with three keys.

    01010011010001010100110001000101010000110101010000100000000011010000101000100000001000

    00001000000010000000100000010000010010111001001010011101010110111001101011001011000000

    11010000101000100000001000000010000000100000001000000100001000101110010011010110111101

    11001001100101010010100111010101101110011010110000110100001010010001100101001001001111

    01001101001000000101010001101000011010010111001101010100011000010110001001101100011001

    01001000000100000100001101000010100100100101001110010011100100010101010010001000000100

    10100100111101001001010011100010000001010100011010000110000101110100010101000110000101

    10001001101100011001010010000001000010000011010000101001001111010011100010000001000001

    00101110010010010110110101100001011001110110100101101110011000010111001001111001010010

    01010001000010000000111101001000000100001000101110010010010110110101100001011001110110

    10010110111001100001011100100111100101001001010001000000110100001010010101110100100001

    00010101010010010001010010000001000001001011100100101001110101011011100110101100100000

    001000010011110100100000001001110100110101101001011011100110010100100111

    LOL 😛 But I'd prefer to have a backspace key as well.

    --------------------------------------------------------------------------
    A little knowledge is a dangerous thing (Alexander Pope)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

  • The topic was use of aliases, and for me, I just make it a habit of using 3 meaningful chars in ALL cases, 4-5 when necessary. Make it a habit and COMMON throughout the entire database, and ALL your code becomes easier to read. (e.g. UserAccounts > usr, WorkPlanGroups > wpg, etc.).

    As far as leading commas vs. trailing, people coming from conventional C++ programmer backgrounds will prefer trailing commas, because it makes things more confusing, makes it easy to miss commas in huge sections of code, etc., and they love that.

    With a leading comma AND a space, code becomes incredibly neat and organized.

    SELECT

    usr.UserAccount

    , usr.UserName

    , ugm.SecurityRightsMask

    FROM UserAccounts AS usr WITH ( NOLOCK )

    INNER JOIN UserGroupMembership AS ugm WITH ( NOLOCK )

    ON ugm.UserGroupID = usr.UserGroupID

    AND ( ugm.UserGroupMemberMask & 4 ) = 4

    OR ugm.UserAccount = 'superuser'

    )

    Note the extra spaces around parenthesis too, and double-indents for subcode sections.

    Whatever you can do to add white space (blanks, spaces, blank rows) ALWAYS makes things more readable.

    I take that to the extreme too. EVERY parenthesis has spaces around it, and every "continued thought" has its connector on the next line:

    SET @Multiplier = CAST ( decimal ( 23, 10 ), @MultiplierText )

    + COALESCE ( @OffsetDefault, @OffsetUserIndex,

    (

    SELECT MAX ( oli.OffsetCoefficient )

    FROM OffsetList AS oli WITH ( NOLOCK )

    WHERE oli.MultiplierFlag = @MultiplierFlag

    )

    , 0 )

    Critics have at it, but the fact is, you can tell what the routine is doing.

    It's quite annoying to deal with:

    SET @Multiplier = CAST (decimal(23,10),@MultiplierText)+COALESCE(

    @OffsetDefault,@OffsetUserIndex,(SELECTMAX(OffsetCoefficient) FROM OffsetList WHERE

    MultiplierFlag=@MultiplierFlag),0)

    The first thing I do when I find other programmer's code like that is add significant amounts of white space and separate it out onto different lines.

    Check any class on good writing techniques. White space is key. And leading commas, plus signs, multipliers, etc. is a excellent and instant shorthand way to identify continuance from a prior line.

    Bottom line is, to each their own. But I never have to spend minutes figuring out code that I did 4-5 years ago. It's all instantly obvious to me and anyone else who looks at it.

    Like it or not, coding is a form of communication. Take a good writing course (not fiction but how to write clear and consise text) and you'll be surprised how it improves your code formatting as well.

    :w00t:

    [font="Verdana"]If technology is supposed to give us more freedom and empower us to pursue the more important things in life, why do so many people allow themselves to become enslaved by it? Always remember, the truly important people cannot be reached... except when they want to reach you.[/font]

  • Sylvia, a well written article, and I can agree with you that using random aliases such as "a", "b", "c" is counterproductive. It's difficult to read and even more difficult to troubleshoot.

    However, I have to disagree with another of your key ideas. Your suggestion that it's not worth aliasing table names simply to save a few keystrokes trivializes the amount of keystrokes one may save over a year, or even a week, by using sensible aliases.

    Many SQL Developers (myself included) must maintain vendor applications over which we have no power to name objects. For example, one of my central applications has table names such as TPB105_CHARGE_DETAIL and TSM180_MST_COD_DTL. It is much easier and faster to type, and frankly easier to read, if I alias those table names using "charges" and "codes".

    Tim Mitchell, Microsoft Data Platform MVP
    Data Warehouse and ETL Consultant
    TimMitchell.net | @Tim_Mitchell | Tyleris.com
    ETL Best Practices

Viewing 5 posts - 76 through 80 (of 80 total)

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