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.
Comments closedNowadays 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.
Comments closedI 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.
Comments closedSebastiao Pereira implements the Ford-Fulkerson algorithm:
Graphs can be used to formulate mathematical models for many different applications and one particular type of problem to be solved deals with networks that transport some kind of resource from one endpoint to another, like water or electricity. Is it possible to create using only SQL Server features?
What’s neat about this is that this sort of flow algorithm also works for, say, complex ETL processes. Also, in case you were as curious as I was, that map is Dresden.
Comments closedTom 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.
Comments closedHugo Kornelis gives an example of a red flag:
This anniversary edition is hosted by Brent Ozar. And his chose topic is: query red flags. Things that make you groan when you open a query and see them in the code. I’m sure there will be a ton of posts, because there are so many. I myself could probably fill a book with things I consider a red flag (and someone else would then point out that my queries have things that they consider red flags, but that is another discussion).
But let’s focus on just one thing in this post.
Hugo selects the case when all query joins are LEFT OUTER joins. Especially when the logic of the query mandates INNER joins.
Meanwhile, if all of your query joins are RIGHT OUTER joins, you’re just chaotic evil.
Comments closedReitse Eskens hits on a long-standing pain point in SQL Server:
In an effort to make code reusable, someone decided to use Scalar User-Defined Functions. About ten of them. And each function was called in the SELECT list of each query sent to the database.
The worst part is, the instincts behind doing something like this are perfectly reasonable. In most programming languages, composing functions or refactoring code into isolated functions that you call is so cheap as to be (almost) free. But in T-SQL, that is rarely the case.
Comments closedChad Callihan tries out a new feature:
Code formatting can be a touchy subject. Sometimes there are clear rules to designate right and wrong, and sometimes there’s not. Tabs versus spaces, anyone?
Surprisingly, SQL Server Management Studio has never had a built-in SQL formatter. Users were always left to use third-party tools or format by hand. But with the latest SSMS 22.7, SQL formatting finally comes built-in.
Let’s look at some examples and see how it performs.
Chad also spotted a problem in the formatter as it is in that release.
Comments closedAndy 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.
Comments closedSebastiao Pereira approximates an integral in T-SQL:
The Newton-Cotes formulas are a useful and straightforward family of numerical integration, which is the approximate computation of an integral using numerical techniques. The numerical computation of an integral is also called quadrature. How can we build these formulas in SQL Server without using external tools?
Click through to see how it all works.
Comments closed