A Fine Slice Of SQL Server
Todd Kleinhans provides a suggestion:
What is a linter? Does it work on actual lint?
No, not really. It comes from what could be called or referred to as best practices or guidance originally from the C programming language to perform static program analysis. It helps prevent bugs and follows the concept of “prevention is the cure”.
Click through for a bit of advice around linting, comments, and more.
Leave a CommentNowadays I look after 3rd-party databases more than internally-developed ones, so I accept there’s a whole lot of ex-best practices, vendor preferences, and possibly shortcuts in queries I might come across – whether it’s a poorly-performing query, a blocker, or an error.
(Although, when I developed software more frequently, I was guilty of all the gripes below. My start in SQL, last century, was poring over a big yellow “For Dummies” book. I was the dummy.)
Click through for the list. I particularly hate tibbling, a rather derisive term for the malformed version of Hungarian notation. This would just lead me down a rant about how systems Hungarian notation was a mess, whereas apps Hungarian notation can be useful in certain circumstances. Tibbling provides no semantically valuable information, which is why I dislike it so much.
Leave a CommentI feel like I should preface this with a disclaimer. I added “potentially-” to the title, because there are many queries that might seem bad but can actually perform just fine. There are queries that on the surface can be great, but are nasty without a particular index, and there are queries that make me cringe a little when looking at them, but are actually okay. Brent Ozar is asking about signs of bad code for this month’s T-SQL Tuesday (the 200th – and I have a response for all 200 if you look back through my history of posts), and he wants us to write this for 2004 Brent, rather than 2026 Brent.
Click through for what Rob has come up with. I agree with all of Rob’s examples and do appreciate his usage of the APPLY operator as a way of solving one common problem.
Leave a CommentLouis Davidson has a list of red flags in code:
I still feel like garbage, so I decided just a simple list would do. I will also preface this by saying each item could include “without a coherent comment.”
Everything on this list fills me with dread unless I read someone say: “Such and such was needed because the optimizer wouldn’t….” and then I at least know why they believed needed it.
Click through for Louis’s list. Most of these aren’t bad things per se, but they do serve as signs of a potential deeper issue.
Leave a CommentTom Zika has a list and starts with AI-generated code:
This one didn’t exist three years ago. Now it’s the first thing I look for.
To be clear, I’m not anti-AI. If the AI wrote clean code, I probably wouldn’t even notice. The red flag isn’t that AI generated it – it’s the patterns that give it away. I recently saw a real case where someone needed to update a set of values. Simple enough, right? Here’s what the AI-generated solution did:
Click through for a laugh, as well as several other red flags.
Leave a CommentAndy Brownsword has it together:
It’s a legacy pattern, and thankfully it’s rare to see these in the wild nowadays.
The legacy
OUTER JOINsyntax (*=and=*) which used to accompany these was deprecated, and finally removed in *checks watch* SQL Server 2012, so that’s one less reason to see the aging syntax.
Every time I see this format, I despise it. Andy explains exactly why. We’ve had better options for more than 30 years, yet people still choose to write code this way.
Leave a CommentJohn Cook muses on regular expression libraries:
The most frustrating aspect of regular expressions is that implementations vary. Features supported in one tool may not be supported at all in another tool, or they may be supported with slightly different syntax.
I learned regular expressions in the context Perl, a maximalist regex environment. This led to frustration when features I expect to work are missing [1]. One way around this is to use Perl analogs of other tools, but this is very non-standard. I want to be able to send colleagues and clients code that works out of the box.
Click through for some thoughts about the lowest common denominator for what products tend to support around regex. This is one of several tricky things when working with regular expressions: you may know a great way to solve a specific class of problem, but does the particular engine you’re using actually support that method?
Leave a CommentAndy Brownsword has a solution:
INSERT statement exceeds the maximum allowed number of 1000 row values
Folks may tell you there are better ways to solve the issue, but the likelihood is that you’re doing it this way for a particular reason. So let’s skip the spiel and jump straight to a simple solution.
Click through to see what you can do to get around that issue.
Leave a CommentLouis Davidson lays out the facts:
Which brings me to the point. There is a myth that goes around that you need to place filters in your SQL statements as early in the statement as possible. Most of this is due to the wild misunderstanding of how a query is executed (versus how your query is processed, which I covered last week.) The actual issue here is that the concept of filtering early is actually true, but certainly not in the way it has been taught.
SQL is a fourth-generation language and implementations approach it. With fourth-generation languages, the actual query you write is not the thing that runs, and there is an entire process to interpret what you wrote and execute operations that meet the intent of your query in the most efficient manner.
Now, this is where someone chimes in and gives all of the circumstances in which T-SQL (or pick your variant) fails to live up to its fourth-generation heritage, such as particularly complex queries, nested views with multiple joins, you using mechanisms that force a specific plan, etc. This is because real life is messy, as Louis shows in some of the examples.
So what’s the point of the first paragraph, then? Because I never miss an opportunity to talk about language generations.
Comments closedRadim Marek lays out a common issue people experience in PostgreSQL:
A
NOT INquery can return the wrong answer without telling you. It is valid SQL, it runs without an error, and it hands back a perfectly well-formed result set that happens to be empty when it should not be. No warning, no hint, nothing in the logs: just zero rows where you expected hundreds, and a database that considers it correct.Almost always the cause is a single
NULLsitting somewhere you forgot to look, combined with two keywords you have typed a thousand times:NOT IN. None of it is a Postgres bug. This is exactly what the SQL standard mandates, implemented faithfully. That is precisely what makes it so easy to walk into, and why the planner could not safely optimize around it for the better part of Postgres’s history. It comes down to oneifstatement in the parser.
This is a Postgres-specific problem, as the same code runs successfully in SQL Server. But if you are working with Postgres, it’s good to keep track of this behavior, and Radim has solid advice for a proper workaround.
2 Comments