21 August 2009

In Defence of Defensive programming

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.

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter

This post has been viewed 1542 times – thanks for reading.

  • Rate
    [Total: 0    Average: 0/5]
  • Share

Tony Davis

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.

View all articles by Tony Davis

Related articles

Also in Blogs

Ten Years Later

It’s hard to believe, but Simple Talk has now been going for over ten years. Thanks to brilliant pieces from our writers, hard work from the team here, and countless valuable contributions from you, our readers, we’re currently receiving one million page views a month, and sitting on a hefty 2,500 articles from over 370 … Read more

Join Simple Talk

Join over 200,000 Microsoft professionals, and get full, free access to technical articles, our twice-monthly Simple Talk newsletter, and free SQL tools.

Sign up