Really cool article, Phil. Are there heresies in SQL Server or, more specifically for most of what I do, in T-SQL? There sure are and some of them are, indeed, version dependent. I also find that what you said about some article somewhere or some old leftover caution from the past which, IMHO, frequently fall into the categories of "not adequately tested" or "not adequately understood" or "Old Wives' Tale based on previous fact" that's no longer appropriate, remaining or becoming a supposed "Best Practice" is spot on and totally annoying.
Some great examples are of such things are (all of which I use at one time or another)...
The proper use of xp_CmdShell, OpenRowset, and the Trustworthy database setting.
The use of the "Quirky Update".
The ability to overlay a variable in the loop known as a SELECT.
The use of SELECT/INTO.
The proper use of EAVs and NVPs.
The occasional WHILE loop as a non-RBAR control method to support "Divide'n'Conquer".
And virtually everything that some nonqual dismisses with the statement "Just because you can do something in SQL Server, doesn't mean you should".
Then there are those things that have, in the minds of many, become "Best Practices" and they go out of their way to advertise such a thing. Two great and seriously annoying examples of that is all the people that write about using Recursive CTE's to provide an incremental count and people that still think that XML string splitters are the way to go (there is a way but that's not what most people are doing)... and they get seriously hostile if you attempt to prove to them that the opposite is true and that their supposed "Best Practice" is a "Worst Practice".
Heh... on that same roll, I "love" it when people say it's a "Best Practice" to base your Clustered Index on the most common queries for the table or to generally avoid the use of IDENTITY as the Clustered Index or that you should never have a Non Clustered Index that duplicates the keys of the Clustered Index or that Temp Tables should never be used or that Dynamic SQL must never be used, etc, etc. Newbies see such rubbish coming from supposedly "experienced" people and take it to the bank not knowing what the actual possibilities are because they've not yet learned that no one knows everything about SQL Server and that "It Depends" is some of the best advice that you'll ever get.
is pronounced ree-bar and is a Modenism for R
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair
When you put the right degree of spin on it, the number 318
is also a glyph that describes the nature of a DBAs job. Helpful Links:
How to post code problemsHow to post performance problemsForum FAQs