T-SQL Development Standards

  • I've been tasked with authoring SQL Development standards for my corporation. I am starting with T-SQL and our proposed code review process.

    I know there are plenty of conversations around best practices, but I am looking for more distillation than that...a guide (especially for non-declarative language developers) on the consistent ways in which code should be developed that doubles (sort of) in the end as a check-list for code reviewers.

    I'm certain at least some of your companies have published development specifications of this sort. What I am looking for at the moment is a cohesive organization for the document. Can anyone lend or point me toward a simple table of contents they are using or know of that I can make use of?

    ------------
    Buy the ticket, take the ride. -- Hunter S. Thompson

  • You might want to read through this post.

    I found it helpful.

    On the second page Jane posted an excellent standards document.

    http://www.sqlservercentral.com/Forums/Topic566838-146-2.aspx

  • Heh... I don't know why people even bother with any standards at all. As soon as something needs to "go in in a hurry", all notions of standards and well intended code reviews will go by the wayside.

    I've attached some standards I wrote for the last company I was at. They're not simple... it covers everything except naming conventions for columns. I used to review every single piece of code that went anywhere near the database. It was a monumental but very effective job. The quality of code went from -50 to 9 on a scale of 1 to 10. No, that's not a misprint... the code was absolute crap, totally unreadable, horribly difficult to modify, full of RBAR, caused an average of 640 deadlocks per day (also not a misprint), and multiple 24-40 hour jobs dropped in duration to an average of less than 30 minutes.

    But, ya gotta be will to and have management's support to just say "NO" to bad code.

    Unless someone comes right out and says "These are the corporate standards and NO code will be promoted unless it meets all of the standards, including required documentation and performance/scalability tests. Your raises and, possibly, your demotions depend on how well you accept and meet these standards", no one will listen and you'll still end up with crap code.

    There's a couple of changes that I actually need to make to the attached standards, but these standards were developed based on things that people did wrong at my previous company. Some of them seem to be just "stupid" because they seem like common sense... but I've found that common sense isn't so common especially in the presence of reluctent/arrogant developers and people trying to meet a schedule at any cost.

    My very best wishes to you on trying to establish standards especially where embedded documentation and format are concerned. It's well worth the gargantuan effort to make the paradigm shift. Plan on becoming both the most loved and the most hated person in the company.

    I've changed all the 3 letter references from my old company to "XYZ"...

    [font="Arial Black"]{EDIT} I've removed the standards document that I had previously attached because it was seriously out of date for what we know today. For example, because of the wishes of my employer at the time, the use of WITH(NOLOCK) was not only condoned, but required in almost every case. Hopefully, we all know better than that today.[/font]

    --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 second Jeffs sentiments. I had to do this aswell, and without management support DB standards get nowhere. Especially in the face of non DB developers and Administrators who think the few chapters they did in their web development course is all there is to DB work.

    Now go forth and fight the good fight for the DBA's 😀

    GOOD LUCK

  • Jeff,

    Thanks for the information. I'm also digesting a couple of books from Mr. Celko that apply.

    As far as the support goes, this wasn't my idea. The idea came out during a meeting between the corporate DBAs, me (they consider me a logical DBA. I've done a lot of administration but do primarily development. They don't develop.) and the person the corporate DBAs report to.

    We, too, have a lot of crap code and I've spent a lot of time rewriting it. We have had standards within our group, but I was asked to develop something more encompassing that would be corporate-wide. The project has the blessing of the CIO and the supervisor of the DBAs (who reports to the CIO) is willing to push the structure and compliance, in part by designating code reviewers in each location and establishing round-robin code review.

    Obviously I am not interested in copying someone else's work but I don't want to completely invent it myself, either. But I was pretty certain that I would get something valuable from this forum.

    Thanks to all.

    ------------
    Buy the ticket, take the ride. -- Hunter S. Thompson

  • Thanks for the feedback, Bryant.

    --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 understand this is an old forum, but in effort to add to a discussion that others may benefit from I want to share a document that might also help. I found the attached standards guide on a search for a similar topic. The link is www.cms.hhs.gov/dbadmin/downloads/sqlserverstandardsandguildelines.pdf. It appears to have been developed for the US Dept of Health & Human Services CMS group. But I think its a simple plain language example of a best practices document.

  • Reading 'XYZ', I'm surprised that the use of views is discoraged.

  • paul s-306273 (11/24/2014)


    Reading 'XYZ', I'm surprised that the use of views is discoraged.

    You read that document (which btw is more than 6 years old) and you were surprised about the use of views being discouraged?

    How about some of the other interesting things in there (no offense meant Jeff):

    All the object prefixes sounds like a blast from the past. I would be surprised if that were something you would put together today as object prefixes tend to make things more difficult.

    How about this one?

    1.All tables (except temp tables and table variables) and views that are not the target object of an Update, Insert, or Delete statement, must be accompanied by the WITH (NOLOCK) optimizer hint. The use of (NOLOCK) without a WITH is discouraged.

    That does not sound like something that Jeff would put his name on today.

    All in all it is a great example of how to put this together but I would be careful about just grabbing that document today and using in any environment.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (11/24/2014)


    paul s-306273 (11/24/2014)


    Reading 'XYZ', I'm surprised that the use of views is discoraged.

    You read that document (which btw is more than 6 years old) and you were surprised about the use of views being discouraged?

    How about some of the other interesting things in there (no offense meant Jeff):

    All the object prefixes sounds like a blast from the past. I would be surprised if that were something you would put together today as object prefixes tend to make things more difficult.

    How about this one?

    1.All tables (except temp tables and table variables) and views that are not the target object of an Update, Insert, or Delete statement, must be accompanied by the WITH (NOLOCK) optimizer hint. The use of (NOLOCK) without a WITH is discouraged.

    That does not sound like something that Jeff would put his name on today.

    All in all it is a great example of how to put this together but I would be careful about just grabbing that document today and using in any environment.

    That last one was because that was a standard heaped on me by an employer. I didn't know enough about it back then to know how bad that little suggestion actually was.

    The point that I believe that you're trying to make is a great one. Understanding changes faster than the actual code and what was right yesterday might not be today... and I whole heartedly agree.

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

  • chenke (2/17/2010)


    I understand this is an old forum, but in effort to add to a discussion that others may benefit from I want to share a document that might also help. I found the attached standards guide on a search for a similar topic. The link is www.cms.hhs.gov/dbadmin/downloads/sqlserverstandardsandguildelines.pdf. It appears to have been developed for the US Dept of Health & Human Services CMS group. But I think its a simple plain language example of a best practices document.

    It might be the machine that I'm on but neither link is working for me.

    --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 (11/24/2014)


    Sean Lange (11/24/2014)


    paul s-306273 (11/24/2014)


    Reading 'XYZ', I'm surprised that the use of views is discoraged.

    You read that document (which btw is more than 6 years old) and you were surprised about the use of views being discouraged?

    How about some of the other interesting things in there (no offense meant Jeff):

    All the object prefixes sounds like a blast from the past. I would be surprised if that were something you would put together today as object prefixes tend to make things more difficult.

    How about this one?

    1.All tables (except temp tables and table variables) and views that are not the target object of an Update, Insert, or Delete statement, must be accompanied by the WITH (NOLOCK) optimizer hint. The use of (NOLOCK) without a WITH is discouraged.

    That does not sound like something that Jeff would put his name on today.

    All in all it is a great example of how to put this together but I would be careful about just grabbing that document today and using in any environment.

    That last one was because that was a standard heaped on me by an employer. I didn't know enough about it back then to know how bad that little suggestion actually was.

    The point that I believe that you're trying to make is a great one. Understanding changes faster than the actual code and what was right yesterday might not be today... and I whole heartedly agree.

    Yes indeed, that is exactly what I was saying. I too once had that nasty little hint crammed down my throat (with much argument from me). I was forced to add that hint to every single query in every single proc (around 6-7k of them) just to turn around 6 months later and remove that hint because it did exactly what I warned them it would. Dirty reads in an OLTP system that handled debit cards for health care benefits. I have written about it before but that was just so nasty. :w00t:

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Just to make sure that no one else falls upon that document, I've deleted it from my previous post. It's just too out of date and that item about WITH(NOLOCK) is just so wrong.

    --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 (10/25/2008)


    But, ya gotta be will to and have management's support to just say "NO" to bad code.

    I completely agree. Standards mean nothing if you don't have the support of management. And to enforce standards, you have to be able to tell people NO from time to time.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • SQLRNNR (11/24/2014)


    Jeff Moden (10/25/2008)


    But, ya gotta be will to and have management's support to just say "NO" to bad code.

    I completely agree. Standards mean nothing if you don't have the support of management. And to enforce standards, you have to be able to tell people NO from time to time.

    Heh... high velocity pork chops literally help drive that point home. 🙂

    --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 - 1 through 15 (of 19 total)

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