The SQL Server community needs to continually test and challenge the established opinions and advice that are passed down to us practitioners. It is for this reason, I’ve always enjoyed Linchi Shea’s approach of patient enquiry to SQL Server issues, and Paul Randall’s noisier ‘DBA-Myth-A-Day’ approach. This is a sign of health, and it is great to see more blogs that take this approach.
It is surprising how quickly advice that is given with the best of intentions can become entrenched as dogma. A Nostrum that is appropriate in a particular circumstance, such as ‘tempdb should have one data file per processor core’, becomes established wisdom long after it has been shown that it is by no means universally true. DBAs need to be confident that ‘Best Practices’ are continually reviewed, and, for developers, that consensus opinion is correct and up-to-date.
When we are developing databases, we are forced to rely on the consensus wisdom of other people’s experience. We haven’t time to test for ourselves that, for example, using two-part names for database objects speeds the compilation of queries, or that recursive CTEs are more efficient than iterative alternatives. We just have to assume that it is so. This shouldn’t stop us checking the occasional assumption and sharing our conclusions.
A short time ago, I decided to collect together, with help from Twitterers, as many ‘SQL code Smells’ as I could. These are those minor signs that, taken together, give the hint that code may need a second look. Code Smells are nothing new and procedural coders accept code metrics based on tools such as FX Cop. I was surprised to see that there was opposition to a mere attempt to list what these practices might be. For me, identifying those factors that make up ‘code-quality’ is the first stage of ‘proving’ them. Established advice is great to rely on, as long as it is continually challenged. If it fails the challenge then it has to be discarded.