A Fine Slice Of SQL Server
Chad Callihan answers one of the mysteries of the universe:
Have you ever seen ANSI_NULLS being set to ON or OFF in a script or stored procedure? I’ve seen or heard it mentioned a few times recently and thought it would be worth demonstrating an example of what this setting does.
Read on for the answer, including a truth table. But definitely keep ANSI_NULLS set to ON, as several important bits of functionality in SQL Server expect it that way.
Erik Darling hits on a bugbear of mine:
A common-enough practice to limit search results is to write paginated queries. You may give users the ability to specify how many rows per page they want to see, or you may have a set number of rows per page.
But the end goal is to allow users to very quickly get a smaller number of rows returned to them. Almost no one needs to get many thousands of rows back, unless they’re planning on exporting the data.
Pagination is such a common activity that I wish there were a way to say, “Here is my data. Hang onto it in memory and quickly retrieve a subset of rows upon request” without doing all kinds of shenanigans on my end. Something like a data snapshot that remains in memory as long as the session is active, until the service restarts, until there is memory pressure, or until the caller manually evicts the data. That would make OFFSET and FETCH really useful instead of barely usable for most pagination scenarios because you wouldn’t need to re-run the entire query for every offset/fetch page.
There are ways to make pagination efficient, but the efficient ones aren’t easy or intuitive.
Comments closedItzik Ben-Gan has a new challenge for the new year:
Packing intervals is a classic SQL task that involves packing groups of intersecting intervals to their respective continuous intervals. In mathematics, an interval is the subset of all values of a given type, e.g., integer numbers, between some low value and some high value. In databases, intervals can manifest as date and time intervals representing things like sessions, prescription periods, hospitalization periods, schedules, or numeric intervals representing things like ranges of mile posts on a road, temperature ranges, and so on.
An example for an interval packing task is packing intervals representing sessions for billing purposes. If you conduct a web search of the term packing intervals SQL, you’ll find scores of articles on the subject, including my own.
Read on for more information about this challenge and one solution to it.
Comments closedDaniel Hutmacher builds a few functions:
As part of spending waaaaaay to much time trying to solve the 2023 Advent of Code challenges, I came across multiple instances where I had to dust off some old math that I hadn’t paid attention to since I went to school back in the 90ies.
So for my own convenience, and yours, I’ve built functions for some common math that you might perhaps encounter at some point. I found this whole experience to be a great way to familiarize myself with a lot of the new functionality in SQL Server 2022, including GENERATE_SERIES(), LEAST(), GREATEST() and more. The Github repo contains a SQL Server 2019 version where I’ve built drop-in versions of the 2022 functions, but they probably won’t perform as well as the built-in stuff.
Click through for demonstrations of determining whether something is a prime number, finding the greatest common divisor and least common multiple, factorization, factorials, and even a bit of combinatorics.
Comments closedErik Darling isn’t content with simple searches:
Like having a built-in type to make dynamic SQL more easily managed, it would also be nice to have some mechanism to manage dynamic searches.
Of course, what I mean by dynamic searches is when you have a variety of parameters that users can potentially search on, with none or few of them being required.
Erik provides two techniques and contrasts the two, so check it out.
Comments closedToday, our elves are working hard on having us come up with the next number in a sequence. Thankfully, they do give us a few good examples to walk through on how they would like us to do it. And with that, it’s off to the races!
Click through for Kevin’s answer.
Comments closedAndy Brownsword takes a closer look at TRY_PARSE():
In the previous post we looked at the functions
TRY_CAST,TRY_CONVERT, andTRY_PARSEand how they compared. I wrapped up and said that my preference for new developments would be to useTRY_PARSEdue to the tighter control which it provides us.As with everything in SQL Server however, there is no ‘best’ approach, it depends. I therefore wanted a separate post to look into the specifics with
TRY_PARSEand areas where it may work more or less effectively.
Andy hits both the good and the bad of TRY_PARSE() and I recommend checking out this post. It’s great for parsing one row or a small number of rows (call it 5-10K or so), and really bad at parsing large numbers of rows.
Kevin Wilkie continues a series on this year’s advent of code. Day 8:
To make life a little simpler for all of us, I used a variable that I just pasted my instructions into. In my case, I called it @FollowThis allowing it to be up to 300 characters in length. Hopefully, no one’s instructions list is over that length – if so you’ll need to make that adjustment.
I also created a variable called @BackUpOfFollowThis that is strictly a copy of the original @FollowThis variable. (No muss, no fuss)
Once I place all of my inputs into the dbo.AOCDay8 table, we can easily start slicing and dicing.
And the more complex form for day 8:
First, we find out that we’re now starting with multiple positions – anything that ends with an A – and finishing anywhere that ends in a Z.
To make this happen, we’re going to want to create another table that will have all of our positions that end with the letter A.
Click through for Kevin’s solutions.
Comments closedI’ve spent a lot of time on this blog telling you different reasons why you should, when you should, and how you should use dynamic SQL.
In this post, I’m going to go into some of the finer points of how I approach dynamic SQL to avoid issues — not performance issues — more procedural issues.
Things you should use to protect yourself from wonky object names, string truncation, object identification, and more.
Read on for Erik’s tips for handling dynamic SQL.
Comments closedChad Callihan looks at a pair of options:
Just because something works doesn’t mean it’s the right thing to do. I had that type of challenge to my database morals recently when facing a query that refused to go parallel.
Read on to learn more. Note that neither of these relates to MAXDOP because that doesn’t determine whether a plan will go parallel (though you can use it to prevent a plan from going parallel).