Click here to monitor SSC

Tony Davis is an Editor with Red Gate Software, based in Cambridge (UK), specializing in databases, and especially SQL Server. He edits articles and writes editorials for both the Simple-talk.com and SQLServerCentral.com websites and newsletters, with a combined audience of over 1.5 million subscribers. You can sample his short-form writing at either his Simple-Talk.com blog or his SQLServerCentral.com author page. As the editor behind most of the SQL Server books published by Red Gate, he spends much of his time helping others express what they know about SQL Server. He is also the lead author of the book, SQL Server Transaction Log Management. In his spare time, he enjoys running, football, contemporary fiction and real ale.

In Defence of Defensive programming

Published 21 August 2009 5:28 am

The relational data model is 40 years old this month, and SQL is not much younger. By any standards, it is a mature, well-documented and well-understood language. So why do we still find SQL code in production that lacks resilience, is so vulnerable to breakage due to unexpected usage patterns, small changes in schema design, or even a standard SQL Server upgrade?

Other languages provide IDEs that will warn you of some vulnerability in your code but the equivalent is not currently available for SQL. There are practices we all engage in that just aren’t good for the long-term resilience of the database. A tool such as SQL Prompt can certainly help you to code quickly and accurately, and to produce maintainable and readable SQL. It can expand wildcards, replacing ‘*’ with a column list, and can dynamically quote identifiers in square brackets, and qualify object names, which will help protect against certain changes. However it can’t prevent you from doing things that are going to return to haunt you. It stops short of warning you if you err from good SQL "best practices": maybe this is a blessing. ‘Best practices’ implies some sort of universally correct way to code, appropriate for all circumstances, and is generally a term that has me reaching for my holster. Of course, the recommendations and advice provided by the likes of Alexander Kuznetsov, Plamen Ratchev, Joe Celko and others are valuable, and all the more powerful for their recognition that there is never a single "correct" way to solve an SQL problem. There are always exceptions to every rule.

Of course, some advice, such as "Don’t shrink your database files" is copper-bottomed. However, for every person who cautions against use of BETWEEN due to inaccuracies when dealing with time, there will be another who reasons that it makes for more readable code. For every piece of sound advice to avoid "*" and always to use fully-qualified object names, there will be equally valid exceptions, such as the need to preserve the contents of a table of unknown structure in a temporary table. Even rules as commonsense as ‘never use cursors’ can have exceptions.

As Joe Celko points out, with traditional programming languages, every time you compile a program, you get the same executable code. It doesn’t work like that in SQL. Data changes, data is added and removed, and query execution plans evolve accordingly. What one moment is sound advice quickly shades to gray and then is soon redundant. Every time a new edition of SQL Server is released, old assumptions (such as ‘never use ‘SELECTINTO‘) are invalidated, although many continue to rely on them.

Defensive programming is vital, but one cannot build resilient code simply by adopting a set of prescribed best practices, but instead by relentlessly revisiting and retesting your code and your assumptions. Or am I wrong? Could a ‘best practices’ feature in a tool like SQL Prompt be made to work, for a language as flexible and dynamic as SQL? What advice do you hear that you think should be emphasized more strongly? Do you know of a ‘best practice’ that should be kicked into touch? I’d love to hear about them from you.

Cheers,

Tony.

6 Responses to “In Defence of Defensive programming”

  1. BuggyFunBunny says:

    >> every time you compile a program, you get the same executable code.

    Strictly speaking, that is less true today than yesterday (measured however one wishes). Dynamic languages, python being close to my heart, don’t. Which is one reason I find databases (including SQLServer) a good match to python.

    The notion of defensive programming (in a database world) most familiar to me is thus.

    Coder: I will do the editing on my screens, you DBA just write out what I send over the wire.
    DBA: up yours idiot. The catalog defines all of the constraints on the data. If you want to provide local editing screens, fine; but all data is checked by the catalog before it’s written. Period.

  2. Alex_Kuznetsov says:

    Tony,

    the previous comment links to an insecure site. Please remove it ASAP! (Ed: I’ve done so. The entire comment has been removed. Oh dear, we’ll have to disable ping-back on this blog. Please don’t think Alex was referring to BuggyFunBuggy’s comments as insecure! It was the comment after his.)

  3. IowaWebDave says:

    I love the intelisense that SQL Prompt gives and I think that having “helpful warnings” and or best practice suggestions could be very beneficial – especially to non-expert SQL writers.

  4. GuinnessFan says:

    As a lone programmer, its tempting to be inconsistent. You’d think it would be easier, but no one is reviewing your code (Until I look at it on Monday morning and wonder what I was thinking.).

    As far as a “best practices” tool, I don’t think all coding is equal. I may have a need to turn it off when creating a new file. Or once I think I’m ready to use this ad hoc query as the basis of a new stored procedure, I could turn the feature on and go through my code and choose to make corrections just like a spell checker.

    I may be inclined to pay for a product/service that gives me a renewed list of best practices and could detect the violations on my list of “violations I want to check”.

  5. Alex_Kuznetsov says:

    Regarding best practices and common vulnerabilities, I like the ReSharper’s non-intrusive approach. When they detect a potential problem, they color code it. You can right click on their warning, choose from one or more alternatives to improve the code, or you can choose to disable the warning instead.

  6. Scott D. Nelson says:

    Shouldn’t this article be entitled “In Defence of Defencive programming” ? :)

Leave a Reply