I tend to worry when people start to mention ‘policies’ and ‘best-practices’ in regard to the development or administration of databases. These are rules, so why not admit it? In many aspects of life we need rules, but in coding with SQL Server there are snags to taking ‘best practices’ too seriously.

I’ll admit that there are advantages were it possible to confidently apply coding rules easily. I’m particularly interested in ways of spotting potential problems in code as part of the development cycle. It is, for example, great to have some generally-accepted ‘best practice’ rules that can be checked as part of continuous delivery or rapid deployment.

So what are the snags?

• SQL coding problems vary enormously in their importance. Leaving out semi-colons scarcely justifies a call to the thought-police, whereas a habit that results in a long-running query such as a non-sargable predicate must be weeded out.
• It is difficult to tie down a rule, to make it ‘hard and fast’. For a rule to be effective, it has to cover all cases. The more one knows about SQL Server, or any other complex, and rapidly-evolving system with a variety of uses, the less inclined one is to lay out a rule, and more likely to start by saying ‘Well, it depends’.
• Rules can change as the database product changes. The old prescriptions against using SELECT … INTO, for example, just aren’t relevant any more.
• ‘Best practices’ can become a comfort that stops us thinking creatively to solve a problem, and can attract foolish managers into believing that it is possible to employ less-expert data people who can just follow rules and step-by-step instructions.
• Static code analysis isn’t going to find the most important problems. The job of checking code-quality is difficult even with procedural code, but even more so with SQL when the query processer will execute quite different query plans from a SQL Query with different volumes and distributions of data.

Perhaps we can present the results of our knowledge and experience in terms of code smells rather than rules. Some coding habits just need investigation and don’t represent a transgression. After all, a smell can come from an excellent cheese or truffle just as easily as a dead rat. Some SQL Server tasks are difficult to automate, but automation can assist judgement and make the task less tiresome, but it can’t replace it.