September 25, 2018 at 10:11 am
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"?
'Ere! Steady on mate, I compiled that one. Well, I wrote quite a lot of it. It was actually about code smells. Things which are OK, but when you find a lot in code, you start to worry. If you have strong feelings on the subject, you can go in and edit the original on Github. I can give you access. Actually, I was only the curator and I do know that some aren't right, but most are.
I discovered another valid use for Select * If you do a OpenJSON WITH, the result is entirely defined in the WITH clause. If you do more than SELECT * you are either repeating yourself or not using a column you've just defined.
Best wishes,
Phil Factor
September 25, 2018 at 8:13 pm
Jeff Moden - Tuesday, September 25, 2018 10:03 AMEric 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).
I do feel what you're serving up, but - blind statements with no backup are nothing more than hot air and cannot be either patterns, anti-patterns or best practices. Done properly those artifacts can be terrifically useful: they are also NOT dogma, so they can be up for debate, voted on, etc...
A proper best practice (a.k.a. guidance document) tends to look a lot like, well - some of your articles, with :
- A central point being advanced
- important aspects to know about that central point
- good things you might want to have in your version of said central topic
- bad things you might care to avoid.
And while it's true that I've heard others misuse the above, I refuse to throw out the terminology simply because someone refuses to look up the definition.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 5, 2018 at 9:55 am
Jeff Moden - Tuesday, September 25, 2018 10:03 AMEric 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).
I do feel what you're serving up, but - blind statements with no backup are nothing more than hot air and cannot be either patterns, anti-patterns or best practices. Done properly those artifacts can be terrifically useful: they are also NOT dogma, so they can be up for debate, voted on, etc...
A proper best practice (a.k.a. guidance document) tends to look a lot like, well - some of your articles, with :
- A central point being advanced
- important aspects to know about that central point
- good things you might want to have in your version of said central topic
- bad things you might care to avoid.
And while it's true that I've heard others misuse the above, I refuse to throw out the terminology simply because someone refuses to look up the definition.
[/quot
The problem with that approach is that misuse is not rare, it's the norm, misuse of "best practice" and "anti-pattern" is far more common that proper use, these things have become dogma with the majority of development managers. For example many people that state "that select * is ALWAYS wrong, that there can be no excuse for using it, no context in which it is useful. Indeed I've seen that particular dogmatic assertion often on this site.
Tom
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply