Formatting and Readability

  • UncleOllie

    SSC Rookie

    Points: 25

    Comments posted to this topic are about the item Formatting and Readability

  • James Stover

    Hall of Fame

    Points: 3343

    I think you can extend this ever further by saying you should take pride in your work. Good formatting and readability is just part of that. Besides, everyone is replaceable. Crafting pristine or sloppy code won't change that fact.


    James Stover, McDBA

  • niall.baird

    Hall of Fame

    Points: 3408

    Its such an easy thing to do, but until you go start trying to maintain code that is poorly written, you'll never really understand WHY you should lay everything out nicely.

    There are a number of tools on the market (or alternatively you could learn how to set Management Studio up properly) which will refactor your code, so basically I believe there's no excuse for poorly indented/laid out code.

    I think every programmer should spend at least a year on maintenance prior to being allowed near a greenfields development!

  • Henrico Bekker

    One Orange Chip

    Points: 27652

    Fully agree.

    Bad formatting is almost as ugly as bad coding. When handing over your code/work you should

    feel confident that any developer can look at it and be able to read/understand it.

    I'm not saying it must be pretty, and spend more time on the looks rather than the functionality, but be proud of your work, not ashamed, and fear that it will come back to you because no one can read it.

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • P Jones

    SSChampion

    Points: 12323

    It's easy to do and only those with no drive or ambition make themselves indispensable - who wants to be slogging away with the same old code in 5 or 10 years time??

  • Koen Verbeeck

    SSC Guru

    Points: 258941

    I couldn't agree more.

    Sometimes the analyst gives me some unformatted SQL code generated by a Build Query editor in SSIS.

    The first thing I do is format the whole thing. While I do this, I read the code which helps to understand it better.

    All my code is neatly formatted. This is extremely useful for debugging and maintenance, but also when you are explaining your code to someone else.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • antiflu

    SSC Rookie

    Points: 48

    One of my colleagues just tested SQL Prompt, it adds a button "Format code". So the formatting is done automatically according to default or custom rules. I used to do formatting by hand, but I'm considering moving to this tool, because it will save a lot of time.

  • Alex Gay

    SSCrazy

    Points: 2182

    I have had to spend hours re-formatting badly set out code to make it readable before I even stand a chance of understanding what it does. Some of it I had written when I was less experienced.

    So, please, set you code out neatly and comment it thoroughly. You will save yourself and others a lot of work in the future. Or do you want to get phone calls on holiday from junior programmers who are pulling their hair out trying to understand your code?

  • IceDread

    SSCertifiable

    Points: 5000

    Interesting that you'd bring this up. Often when I see bad sql code it's done by someone who never took the time to learn sql but focuses on vb/ java/ c or c#. There it's more common with bad coding standards I'd say from my experience. What do you think?

    I also think it's more common with this type of code from people who wants to have fun more then having fun while taking pride in doing it proper. Often the later also makes faster code as a side bonus.

    I changed a sql script from taking 3 hours down to 40 seconds. That was such a person. And guess what, cursor was involved, but the time consuming part was not the cursor but how the select statements were made.

  • antiflu

    SSC Rookie

    Points: 48

    Oh, I just discovered there is more than one.

    See this Stack Overflow question for a comprehensive list of SQL formatting helper tools.

  • blandry

    SSCarpal Tunnel

    Points: 4761

    As much as the content of this editorial is good, I am amazed, stunned and disappointed that there is not even ONE single mention of the most basic element of programming - commenting your code!!!

    In my 30 years of development and managing programmers I have driven plenty of them crazy when I have rejected code that is not commented. PROGRAM CODE IS A COMPANY ASSET - and thus commenting your code is as important as being able to understand a corporate financial statement because it is explained.

    Commenting code not only benefits any new developer being able to pickup and manage code, it also helps the very developer who creates the code. When you have been at this as long as I have, you are going to run into times when you are asked to go back to old code (sometimes even a decade old) and make changes - when you comment your code you capture thoughts of what you were doing and what your approach was.

    To pen an editorial about "readability" is nice - but it is NOT readability that truly matters - code comments do, and as I have said and will stress again - its a company asset, developers don't "own" code, the company does. Just as you might have some new appliance added to your house and then have it explained by the installer, code comments are simply essential and the FIRST element to any "readability".

    Its a shame that he author left this out of the editorial - thats like talking about a sandwich and never mentioning bread.

    There's no such thing as dumb questions, only poorly thought-out answers...
  • graymer

    SSC Eights!

    Points: 975

    READABILITY is a must. I can't stand coming in behind someone else and trying to figure out where the end of a statement is or if they've got useless SQL crammed into their horrible coding.

    Commenting is a must too, it's so easy to put in a '--end of blahblah IF statement' to make things so much easier to find. Sometimes you feel like commenting of something like that is silly, but when you're hunting through hundreds of lines of SQL later and you're wondering where you're at it's nice to know that you terminated your IF statement.

    I have a few things I like to do make things more readable too, like using tab to line up all my data types in a declare block. I know it takes up space doing it each variable on it's own line but it makes it a lot easier for me to read.

    I do the same with my inital sets too where I tab out after my variables and line the equals up.

    Another quirk I do is put my commas at the beginning of each line in a declare so if I have to comment out a variable that I'm no longer using. It's all on one line and I don't have to worry about forgetting it and errors popping up to remind me.

    Also, I think it's important to capitalize keywords too. Something handy for that is using highlight the keyword you want to upper case and use Ctrl+Shift+u. Back to one of the the reasons I tab out my data types in my declare if I'm going back through my SQL and I'm cleaning it up I can use a Alt+'mouse select' and Ctrl+Shift+u and it'll upper case the highlight block easily.

  • roger.plowman

    SSChampion

    Points: 10149

    Amen to readability, and a double-plus-amen to comments.

    You need *both*.

    I've been in the unfortunate position of having a boss who considered comments nothing but time-wasting fluff. He reprimanded me for puttting *too many* comments. 🙂

    Three months later commenting was a best practice at that company. I like to think I had some small part in that...

    Seriously, nicely formatted code makes the intent clearer. Correctly indenting code makes the intent of the code clearer. Comments explain the intent.

    What part of clear code and explanation of intent is bad?

    I also have had to come in after other programmers and take on huge projects that liked to use X, Y, I, Amount, etc as variable names. Yuck.

    I'll say it again. Clarity of code = clarity of purpose. Comments = explanation of intent. This is not something anyone should be debating. The *hows*, yes. Not the *if*.

  • antiflu

    SSC Rookie

    Points: 48

    By the way, something that helped me is starting to use Common Table Expressions (CTE). It really helps to organize heavily nested code and make it readable.

    (For those who don't know what I mean:

    WITH a AS ( complex query ),

    b AS ( complex query ),

    SELECT a.*, a2.key

    LEFT JOIN a ON criteria AS a2

    LEFT JOIN (SELECT * FROM a INNER JOIN b)

    etc...

    You can use the expressions multiple times in the query afterwards but even when you don't, it already becomes more readable.

    Has anyone got experience with the automatic query formatters? I already found out that the one by Red Gate (SQL Prompt) is working well, the cheaper one SQL Pretty Printer already caused some crashes...

  • Jeff Moden

    SSC Guru

    Points: 994680

    Agreed... both formatting and appropriate commenting are essential ingredients to good code.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

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

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