Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: 335 | Views in the last 30 days: 5
 
Related Articles
FORUM

Difficult recursive query problem

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

FORUM

query problem

problem in my query

BLOG

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...

FORUM

Remote SQL Version Query Problem

T-SQL version query Problem

FORUM

Best Practices

Best Practices for .NET integration

Tags
database weekly    
editorial    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones