Guest Editorial: On writing SQL

  • Heh, are you slinging swine online again Jeff? :hehe:

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • I'm acutely aware that there are a number of tricks to being a highly productive SQL Programmer, and I cringe at the thought that I only know a few of them. So, what are they?

    Heh... it's been my experience that so called "highly productive SQL Programmer's" know nothing of proper documentation, "negative testing", nor performance or scalability. I've seen it time and again... a "highly productive SQL Programmer" will turn code in on time, get praise and kudo's for doing so, and never be reprimanded for the multitude of failures found in test, or worse, in production nor ever be sought out to fix their own bloody code when performance or scalability problems arise. Nor will they be made to suffer any consequences when their code takes hours to research for a modification by someone else because there's no documentation in the code.

    Shifting back to the subject at hand, my saving grace is well tested/documented code snippets, standardized headers, and several other "goodies" stored on a memory stick. For example, does anyone think that I actually type the code to build a Tally table or to do even a simple split anymore? CPR (Copy, Paste, Replace) has become a real friend.

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

  • Jeff,

    I'm a template Junkie. Where I can I do drag n' drop from the template browser from my own custom macros, and execute the macro rather than CPR and get all the parameters such as table names filled in with the macro-substitution. After all, every many-to-many table looks pretty much alike. Best damned feature in QA/SSMS.

    Best wishes,
    Phil Factor

  • Heh.. see? You've got nothing to worry about, ol' friend... you know all the tricks. 😀

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

  • Jeff Moden (12/12/2008)


    Shifting back to the subject at hand, my saving grace is well tested/documented code snippets, standardized headers, and several other "goodies" stored on a memory stick. For example, does anyone think that I actually type the code to build a Tally table or to do even a simple split anymore? CPR (Copy, Paste, Replace) has become a real friend.

    Hell I was giving you more credit than that (as in - why are you continuously building tally tables:D):w00t::cool::hehe:

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Phil Factor (12/13/2008)


    Jeff,

    I'm a template Junkie. Where I can I do drag n' drop from the template browser from my own custom macros, and execute the macro rather than CPR and get all the parameters such as table names filled in with the macro-substitution. After all, every many-to-many table looks pretty much alike. Best damned feature in QA/SSMS.

    Oh My Gawd I never even thought of that. Thank you thank you.

    😎 Kate The Great :w00t:
    If you don't have time to do it right the first time, where will you find time to do it again?

  • Jeff Moden (12/12/2008)Shifting back to the subject at hand, my saving grace is well tested/documented code snippets, standardized headers, and several other "goodies" stored on a memory stick. For example, does anyone think that I actually type the code to build a Tally table or to do even a simple split anymore? CPR (Copy, Paste, Replace) has become a real friend.

    Ugh. In addition to this, I so agree with all of the points in your post. But about this, I am a software developer and I sometimes work as a contractor. Sometimes I am called in for a technical test, and almost always, they disable help. They want me to recall the properties of a file object, or remember the syntax of a VB6 file open command from memory. Are you people crazy? I wrapped that crap in a class back in 1994 (with latency and error handling galore) and I haven't looked at it since. You interviewers have it all wrong. My power is on my memory stick, and it has nothing to do with programming this or that stupid little thing.

  • Heh... I also keep a read-only CD handy for those places where they don't allow you to carry in or out any type of writable media. I've even had places reject that idea because they're worried about someone bringing in a trojan horse or software "spy", etc, etc. Then, ya just gotta remember how to do stuff... 😀

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

  • I also keep a read-only CD handy for those places where they don't allow you to carry in or out any type of writable media

    Nigel 'Pop' Rivett told me that he started his internet site just to get round this sort of difficulty of having a limitation of what you can bring to a site. Instead of a CDROM or thumb, he just put his stuff on an internet site, and it was a foolhardy employer who stopped you accessing the internet when all useful help is now online. He was amazed when his site became so popular. The answer was simple, it was useful to him, and it was equally useful to any contractor.

    Best wishes,
    Phil Factor

  • gcopeland (12/12/2008)


    You said, "The march of technology makes bone-heads of us all." I take your meaning but I must disagree. The fact that changes in technology make our knowledge obsolete indicates that much of what we know really isn't knowledge at all. I believe this is one of the fundamental reasons that technical and non-technical people so frequently cannot connect at a personal level. Technical people sometimes scoff at, say an art history major, but at least what that person studies and contemplates will still be true a thousand years from now. Much of what we study will be obsolete within the decade.

    Some may not care about this, but I found this realization somewhat depressing.

    That's assuming that the study of art contemplates some sort of actual truth, instead of a contrived truth. Subjective truth isn't truth, it's perspective.

    ---------------------------------------------------------
    How best to post your question[/url]
    How to post performance problems[/url]
    Tally Table:What it is and how it replaces a loop[/url]

    "stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

  • Jeff Moden (12/12/2008)


    I'm acutely aware that there are a number of tricks to being a highly productive SQL Programmer, and I cringe at the thought that I only know a few of them. So, what are they?

    Heh... it's been my experience that so called "highly productive SQL Programmer's" know nothing of proper documentation, "negative testing", nor performance or scalability. I've seen it time and again... a "highly productive SQL Programmer" will turn code in on time, get praise and kudo's for doing so, and never be reprimanded for the multitude of failures found in test, or worse, in production nor ever be sought out to fix their own bloody code when performance or scalability problems arise. Nor will they be made to suffer any consequences when their code takes hours to research for a modification by someone else because there's no documentation in the code.

    Shifting back to the subject at hand, my saving grace is well tested/documented code snippets, standardized headers, and several other "goodies" stored on a memory stick. For example, does anyone think that I actually type the code to build a Tally table or to do even a simple split anymore? CPR (Copy, Paste, Replace) has become a real friend.

    Yeah, that's way too time consuming, so I wrote a function that produces the code to build a tally table one character at a time by using a tally table . . .

    ---------------------------------------------------------
    How best to post your question[/url]
    How to post performance problems[/url]
    Tally Table:What it is and how it replaces a loop[/url]

    "stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

  • Here are a couple of things I find useful when developing queries

    Dynamic queries

    Print any dynamic queries before executing them

    Queries called via osql

    If a query will be called via osql, I use lots of PRINT statements like "about to lookup X and Y..." and make sure I redirect the "messages" output to a file. If (when) things go wrong in production I will have a log file that tells me exactly how far the query got, and usually the SQL server error message as well.

    Comments

    Many things that made me think for more than 5 minutes generally get a comment explaining what's going on

    When I'm almost done:

    If the query is more than a hundred or so lines long, I add a comment at the top with the high-level logic. This logic often follows directly from the requirements for the query and generally takes little effort to maintain. Even if it is a little out of date it is MUCH better than nothing at all. This has been very useful for others (or myself a month or so later) to understand the code. Here is an example:

    "

    This query compares the XYZ database with recent user exports for these domains:

    (list of active directory domain follows).

    Compare the weekly domain user export (in xls format) with the database and log discrepancies (in xls format) as follows:

    Additions, Deletions, Renames, Updates

    For the logged Deletions and Updates also update the database

    "

    At the end of a self-contained section

    A "closing" comment, saying (for example) " This completes the preparation of the #XYZ temp table.

  • One thing that I have started to do in the last two years that I have found to be very helpful is to include test commands in the comments for Proc's, Triggers and UDF's. For work in a product or application database, it can often be more work to determine/develop valid test statements than it is to do the rest of the debugging or troubleshooting.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Interesting items on comments. I tend to avoid comments unless things are complex, so the part about writing one when you think more than 5 minutes makes some sense to me.

  • rbarryyoung (12/15/2008)


    One thing that I have started to do in the last two years that I have found to be very helpful is to include test commands in the comments for Proc's, Triggers and UDF's. For work in a product or application database, it can often be more work to determine/develop valid test statements than it is to do the rest of the debugging or troubleshooting.

    Funny that you should mention that... I thought I was the only one that did that.

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

Viewing 15 posts - 31 through 45 (of 73 total)

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