Whenever I get a spare moment, I check out some of the classic SQL Server 'heresies', to see whether they are still valid. SQL Heresies start out innocently, sometimes as a blog post or a paragraph on MSDN, but they soon pass into the pool of shared wisdom, of DBAs and developers. Over time they ossify to the point where they are treated as holy writ.

Always declare your variables at the start of the batch! (Yes, it's a better style perhaps but doing otherwise no longer triggers a recompile). Never use SELECT * (there are rare occasions when it is correct to do so). Never have a USE command within a batch (why ever not?). Never use scalar functions for maintaining global constants; (the problem is actually one of using unverified functions that aren't schema-bound).

Although the marketing people at Microsoft like to trumpet the 'big new things', such as Query Store, the real pleasures of upgrading SQL Server is in noticing that some of the little problems were fixed. Often, a sticky problem is solved, but goes unannounced save for a hand-waving mention of 'performance improvements'. Stuff gets fixed, but the 'Don't do x' code commandments of the SQL tribe never seem to get updated to read, 'Before SQL Server 2008 r2, don't do x'.

Another problem with creating a SQL heresy is that many of the things that look wrong are perfectly fine, in the right circumstances and when done by an expert. Even the dreaded NOLOCK habit can't be anathematized entirely, because Microsoft use the hint extensively in their system stored procedures. You need to hedge what you say with 'ifs' and 'buts', or like Paul Randal, sigh and say, 'it depends'.

There is usually a reason for everything in SQL Server, though one sometimes struggles to imagine what it was. Occasionally, we have to tell the indignant cub developer who has just used an OPTION (HASH GROUP, FAST 10), 'Sorry but that is for expert use only, and you ain't quite there yet.'

There is no shame in this. Many of the dark corners of the SQL cupboard are intended for use by the programmers who maintain the rich undergrowth of system procedures, views and functions. Whereas we blink in some perplexity at their code, they would be just as baffled were they to try to understand some of the SQL tricks we employ in support of the work of the average commercial organisation.

So, let's qualify our favorite SQL heresies with the versions to which they apply, and the usage for which they are bad. Otherwise we risk losing the wonderful spirit of invention that has produced so many of the interesting techniques that have made our working lives easier and more interesting.

Phil Factor.