REPEATABLE READ

define:idiom "An expression whose meanings cannot be inferred from the meanings of the words that make it up."

In his Guru Book devoted to SQL Server Stored Proceedures (and other goodies), Ken Henderson addresses the practicality of using design patterns in SQL Server. As part of his introduction to the legitimate use of design patterns with T-SQL, he pauses for a minute to discuss the notion of idioms in SQL Server. Idioms, an idea he gets from Brian Kernighan's and Rob Pike's The Practice Of Programming, are like "mini patterns or pattern fragments" which " have more to do with [a particular] language than with solving a particular kind of problem."

In other words, Idioms are 'best-practices' solutions to common code requirements made by experienced developers. Ken Henderson says idioms should be considered the tools that make up the toolbox of a seasoned developer. For example, a common task in T-SQL is the creation of an object (table, view, sproc, udf, constraint, etc). You may have seen something akin to the following (which checks to see if a table exists before attempting to create it):

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'authors' )
   DROP TABLE authors

CREATE TABLE authors (….. -- etc

I won't go into minute detail about why the above is bad. Hopefully everyone can spot the issues with such an approach. Ken Henderson actually talks about the idiomatic solution for object creation at length. In the end, after discussing the faults of a number of approaches, he delivers this:

IF OBJECT_ID('dbo.authors') IS NOT NULL DROP TABLE dbo.Authors
CREATE TABLE dbo.authors ( … -- etc. 

In addition to being a bit more succinct, the above approach is much more impervious to change between versions of SQL Server (or should be).

My only complaint with Ken Henderson's coverage of idioms is that he didn't devote MORE space/time to them. The idioms he provided were great.

I'm not even going to pretend to being on par with Ken Henderson, but I do feel comfortable proposing idioms in this blog. To that end I've created a new post category called (tada!) idioms. The idea is that I'll do a bunch of research on common problems that should probably have idiomatic solutions. I'll post my results here, and hopefully with some feedback we'll all be able to learn something and arrive at the best idiomatic solution.