September 22, 2018 at 1:01 am
Comments posted to this topic are about the item Are there such things as SQL heresies?
Best wishes,
Phil Factor
September 22, 2018 at 12:43 pm
Great article - In my experience, the subset of "heresies" that should be discussed under the "it depends" clause is almost always substantially larger than the actual heresies. And as you point out, once you start discussing the impact of versions, that subset grows even larger.
There are definitely some hard and fast "don't ever do this/make sure you always do that" rules, but, again, my experience has been that those are the minority. At the same time, as you also noted, sometimes the "it depends" clause is definitely somewhat esoteric, or beyond the skills/experience/needs of the majority of users, in which case it often is easier to say, "treat this as a hard and fast rule." As with almost everything that reaches a certain level of complexity, things almost never fit neatly into a specific category (heck, notice how many times I had to put qualifiers on my statements here 🙂)
September 23, 2018 at 2:58 am
Wise words Phil. Things would be a lot easier (and musch quieter) if we would only talk about things we actually know about in stead of just "parrotting" words of others (who sometimes don't know what they're talking about but just repeat what they heard from "experts").
However I learn a lot from those comments, even if it's just how NOT to do it :-). Keep an open mind.
Ben Gijsen,
" Life is like a Ferrari, it goes too fast,
But that's okay, you can't afford it anyway "
- Garfield the Cat-
September 23, 2018 at 10:06 am
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.
Dynamic SQL
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.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 24, 2018 at 4:54 am
Nice post Phil (and follow on Jeff).
The corollary could be - are there any actual best practices? In general there's a bunch of stuff you can generally do that will mean you won't go too far wrong. I can't say there's many things you can say - you should invariably do this...
September 24, 2018 at 6:51 am
call.copse - Monday, September 24, 2018 4:54 AMNice post Phil (and follow on Jeff).The corollary could be - are there any actual best practices? In general there's a bunch of stuff you can generally do that will mean you won't go too far wrong. I can't say there's many things you can say - you should invariably do this...
Here's one: Set SQL Coding standards with justification for each one and follow them. Wrong/right/otherwise, be consistent.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
September 24, 2018 at 7:20 am
So much truth in those two words: "It depends." 🙂 I repeat these words often when discussing SQL-related topics with my coworkers.
September 24, 2018 at 7:42 am
Good luck stomping them out. Far too many people refuse to let go of their outdated information. I recall a particular instance in 1996 when someone was instructing new support people that the only time a drive could be safely set to boot was when originally formatted. Of course showing her documentation from years earlier when that changed was of no use.And when it was demonstrated that one could do it, well I just got lucky. Over and over.
I would love to know more about only declaring variables near the top. I had assumed that was held over from the days of compiler requirements. Some still say that should be the cast in C# even though many others insist a variable should be declared as close to first use as possible.
September 24, 2018 at 8:02 am
I would still caution that too many "it depends" becomes "I'll do it like I want without understanding why the best practice is the best practice." Just an observation that I've made all too often in real life. Maybe better to phrase "normally this way but we need an exception here because..." I occasionally use NOLOCK under very select circumstances. But I don't proceed from there to say "it depends." I would say NOLOCK is generally a bad idea because..... However, in this case it's okay because ….. That's not the same as "it depends."
September 24, 2018 at 8:45 am
RonKyle - Monday, September 24, 2018 8:02 AMI would still caution that too many "it depends" becomes "I'll do it like I want without understanding why the best practice is the best practice." Just an observation that I've made all too often in real life. Maybe better to phrase "normally this way but we need an exception here because..." I occasionally use NOLOCK under very select circumstances. But I don't proceed from there to say "it depends." I would say NOLOCK is generally a bad idea because..... However, in this case it's okay because ….. That's not the same as "it depends."
That's also how new and terrible "Best Practices" are born. And, yes, I agree.... "It Depends" is correct but can be abused like anything else. Eventually, people need to get something done and that means that someone eventually has to make a decision. As we all know, it's far too often that decision is made by people that aren't actually qualified to make such a decision.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 24, 2018 at 9:38 am
That's also how new and terrible "Best Practices" are born. And, yes, I agree.... "It Depends" is correct but can be abused like anything else. Eventually, people need to get something done and that means that someone eventually has to make a decision. As we all know, it's far too often that decision is made by people that aren't actually qualified to make such a decision.
The problem with documenting heresies, questionable design decisions and SQL Code smells is that the message has to be simple. If you launch straight off into an elaborate explanations with all the 'if's and 'but's, then you lose the audience or reader. When I write an article about a heresy, I like to start with the simple message such as "don't use NoLock unless you can justify it successfully", and then follow that with a paragraph or two that gives a bit more detail and maybe a hint that there may be a use for the heresy. Only then do I give the reader both barrels of explanation, preferably with a SQL Demonstration of what happens when they get it wrong.
Best wishes,
Phil Factor
September 24, 2018 at 11:37 am
There may be times, but in his infinite wisdom, Paul rarely says "It depends" to Kimberly.
September 24, 2018 at 6:58 pm
Phil Factor - Monday, September 24, 2018 9:38 AMThe problem with documenting heresies, questionable design decisions and SQL Code smells is that the message has to be simple. If you launch straight off into an elaborate explanations with all the 'if's and 'but's, then you lose the audience or reader. When I write an article about a heresy, I like to start with the simple message such as "don't use NoLock unless you can justify it successfully", and then follow that with a paragraph or two that gives a bit more detail and maybe a hint that there may be a use for the heresy. Only then do I give the reader both barrels of explanation, preferably with a SQL Demonstration of what happens when they get it wrong.
Heh... I read the first sentence of that and immediately started to reply but then read the rest. My comment was going to be "Not only do they need to be simple, they actually do need to be correct and the only way to prove that is to back them up with a well though out, repeatable, and demonstrable test because 'One good test is worth a thousand expert opinions.'"
That also brings up another sore spot with me that's still related to supposed "Best Practices"... just because someone has written a "Holy Grail" article with all sorts of test code, it doesn't mean that they're necessarily good tests. There have been a huge number of "splitter" tests done and the conclusion was that the XML splitter was the fastest. I pity the poor slobs that listened to that garbage because, while most of the tests done had substantial numbers of rows of data, all the rows were either identical or had a total cardinality of something less than 6. When I did a more real life test, the XML splitter sucked so bad that it developed its own field of gravity. 😀 Then when you try to identify what the author did wrong, they say stupid stuff like "Well, I'm not going to test it again" or other lame thing and they shut down the post for comments and then publish their junk in a book.
Like my Dad used to say... "Half of all that is written is incorrect. The other half is written in such a way that you can't tell". That's why good tests are so very important.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 25, 2018 at 8:51 am
When we speak of T-SQL "heresies", it seems we're speaking of anti-patterns. Some things developers do (like inline sub-queries, scalar functions, and SELECT *) greatly simplify the code and may not negatively impact performance in any noticable way at small scale, but it doesn't scale well for larger datasets. The problem is that code originally intended for one limited use case (querying a handful of rows for one customer id, a handful of times per minute) can later by copied and retrofitted for an entirely difference purpose (aggregate reporting on customers or high volume transactions). It's important that we don't blindly adopt patterns or reuse code without understanding it's function and limitations.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 25, 2018 at 10:03 am
Eric M Russell - Tuesday, September 25, 2018 8:51 AMWhen we speak of T-SQL "heresies", it seems we're speaking of anti-patterns. Some things developers do (like inline sub-queries, scalar functions, and SELECT *) greatly simplify the code and may not negatively impact performance in any noticable way at small scale, but it doesn't scale well for larger datasets. The problem is that code originally intended for one limited use case (querying a handful of rows for one customer id, a handful of times per minute) can later by copied and retrofitted for an entirely difference purpose (aggregate reporting on customers or high volume transactions). It's important that we don't blindly adopt patterns or reuse code without understanding it's function and limitations.
Heh... "Anti-Pattern". That's another term that bugs the hell out of me as bad as "Best Practice" does. It's a short way of saying that it's not a best practice and, just like a "Best Practice", has become a "macro" in the language that many adhere to with no explanation.
Take SELECT * for example. People have been made to be absolutely paranoid about ever using it and some "unaware" DBAs simply won't allow it. And, yet, it CAN be a marvel of performance when used in such constructs as WHERE NOT EXISTS and even in very wide tables where code does actually need to return all columns (especially for Temp Tables which usually contain only the columns that are actually needed). Listing all of the columns in a very wide SELECT list actually causes the code to run (sometimes significantly) more slowly than the correct use of SELECT * even when it's coming from an ORM especially if there's a recompile involved.
RedGate has that book out on 101 anti-patterns and there are a few that made me more than wince. I don't recall what they are but I do recall saying to myself, "That's not right... how the hell did they come to that conclusion"?
Yes, I realize that both "Best Practices" and "Anti-Patterns" are meant to be helpful guidelines to keep (especially newbies) folks out of the woods when it comes to certain things but a lot of people don't consider them to be mere guidelines. After all, consider the target... they're people that don't know. Instead, they consider them to be rote commandments from the "SQL Bible" and follow them to a "T", especially when the author or source is perceived to be some kind of authority on the subject, and that's just not right... especially since many authors and other "authorities" are actually incorrect or have as little understanding as the people they target with their articles (again, like the problems with splitters that many articles have perpetuated).
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply