It always surprises me when the old topic of SQL coding standards gets such a good response. I've gone on about it on several occasions, usually after coming across some particularly egregious examples of bad work. However, the enthusiastic reaction to Robert Sheldon's recent 'The Basics of Good T-SQL Coding Style' shows that a good message bears repeating, especially when written so meticulously. By 'Coding Style' we're not talking just about code formatting and documenting, because even if you get that right, you can still fail if the algorithm you use is wrong, or if you fail to consider the database design. Actually, there are plenty of ways of goofing up with SQL. In my SQL Code Smells booklet I list over a hundred of them. The great SQL Blitz is growing into an excellent way of seeing the extent of the technical debt in a SQL Server database. Nasty database surprises can thereby be turned into the preferable, but still horrible, database revelations.
Although there is a lot you can do with SQL Server Policy-based management, you can extend your reach with static code analysis before a build, and there are a whole host of tests you can devise at build-time, to spot such danger-signals as table smells.
I admit that I've spent quite a while pestering the good people in Redgate's SQL Prompt team about SQL Code Formatting. I'm keen on formatting: in the days before Microsoft released their .NET SQL parser, I even wrote a rudimentary SQL-based SQL Code prettifier and formatter. I've always loved automatic code formatters even in the dark days when I programmed in C for a living. The code formatter in SQL Prompt was always useful but a pernickety coder such as I always had to tidy up the result afterwards. After an enormous amount of work, the formatting feature in Prompt has been re-written to bend to nearly all the bizarre and wildly-varying SQL Coding preferences that have been suggested by the existing community of users. It copes with practically all my preferences and I can even indicate sections of code that it should leave out.
Like many programmers, I get bored easily. We're not all naturally 'detail' people. If a job can be automated, then it should be, I reckon. Checking for coding style and reviewing code is so much easier when using techniques and tools to help, as long as the tools are your slave, and not your master. Policies can destroy creative thought if applied too mechanically. In a complex system, such as a database, best-practices so often blur into 'it depends'.
Phil Factor.
