SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL Best practices, policies and code-smells.

By Phil Factor,

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.

Total article views: 462 | Views in the last 30 days: 1
Related Articles

Difficult recursive query problem

I'm trying to query our health care database to find the set of patients who meet certain diagnosis ...


query problem

problem in my query


Remote SQL Version Query Problem

T-SQL version query Problem


T-SQL best practice SQL server

SQL server T-SQL best practice This month’s TSQL Tuesday party is being hosted by Amit Banerjee (Bl...


Problem in Join Query

Problem in Join Query

database weekly