I've been quite enjoying the task of writing about SQL Code Smells, recently, because of the increasing interest in the topic. My contribution to this over the past few years has been less like creative writing, and more like a continuing task of curating and promoting the ideas of other people. I've been maintaining a live list of SQL Code Smells on Github, which is then published on Simple-Talk and as a pdf. It started out in 2014 as a small booklet and has gradually increased in size, over the years.

Last year, SQL Prompt quietly introduced some code analysis capabilities. As you type in code, it underlines any code it considers 'suspect'. It introduced the idea of SQL Code analysis to a new audience of developers, who were surprisingly receptive to the idea that some of their code could be improved. Even I was amused to find how much of my own code was riddled with smells!

Redgate asked me to help to document the code smells that SQL Prompt detected and relate them to what was already in the SQL Code Smells booklet. I've also written several longer, explanatory articles on some of the common code smells. The reason I enjoy writing these longer articles is that there are constant surprises. Some things one is warned about turn out to have been long-dead implementation issues, whereas others that seemed marginal turn out to have the potential for dire consequences. Many coding patterns that have been promoted as best practices are highly debatable. I'm constantly finding out new things.

There are several times in the database cycle where it is good to have feedback about code quality. It is often useful to have it as we develop code, though it is occasionally distracting if the code you're writing is just temporary stuff, a one-off hack, or for testing. It is certainly a good idea to know about the issues within code that is part of a build, such as use of deprecated syntax, or SELECT *, or NOLOCK hints, because it is code that is checked in, and part of the trunk. The command-line version of SQL Codeguard will do this for you very simply.

Static code analysis has its limits, of course. You can extend what can be detected by analysing the live database, via the metadata. However, this still doesn't yet detect some of the worst crimes, which have little to do with syntax, but are errors of style or algorithm. It is difficult to identify these 'greater' code smells, mechanically. How do you detect the crime of using polymorphic association, or 'God Objects'? Unless we can find ways of detecting these greater heresies, we may be in danger of distracting developers away from the real problems, and towards the less significant details.

I like to think that simple code smells, such as excessive table width, often serve to indicate the possibility of more complex smells such as 'Use of God Objects' and other normalisation errors, although this isn't always true. Is there more we can achieve with code analysis?

Phil Factor.