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 ‘SELECT…INTO‘) 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.