A Fine Slice Of SQL Server
Erik 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).
Alternate title: How do I tell how far I am on that command?
This little command (frequently with a WHERE clause) is a great way to tell how far along you are on any given command. Specifically, I’ve been running a bunch of DELETEs recently. I’ve got something like 5-50 million rows of data to delete and I’d like to know how far along I am. A common way to do this would be just a simple count.
Click through for Kenneth’s technique, as well as good information from Jeff Moden in the comments.
Not mentioned in this post is that hopefully, your massive delete operations are running in batches, as trying to delete 5 million or more rows in a single DELETE command is…resource-intensive.
Comments closedKevin Wilkie continues the 2023 advent of code. Part 1 of day 7 covers card hands:
I have my version of the Day 7 data in my handy dandy table called AOCDay7. This time we’re being asked to figure out what kinds of Camel hands are given to us and then told to rank all of them.
Let’s start by aggregating and analyzing the data a little bit.
This time, our wonderful friends the elves have decided that they don’t like Jacks but they do like Jokers. So, there are a few changes to our code that will need to be made:
Click through for Kevin’s solutions to these challenges.
Comments closedThere are two forms of conditional logic that I often have to fix in stored procedures:
- Branching to run different queries at different times
- Complicated join and where clause logic
The problems with both are similar in terms of performance. You see, when smart people tell you that SQL is a declarative language, and not a procedural language, they’re usually trying to get you to stop using cursors.
And that’s not always wrong or bad advice, trust me. But it also applies here.
Read on for exceptions to the rule and how you can make your life a bit easier if you do have this in place.
Comments closedAndy Brownsword tries and tries and tries again:
In the previous post we looked at how
ISNUMERICandTRY_CASTwork and why we may want to utilise the latter when building validation for our data. When SQL Server 2012 rolled around it wasn’t onlyTRY_CASTwhich was added, we also hadTRY_CONVERTandTRY_PARSEintroduced too.Here we’re going to look at how those function and the differences in the outputs which they can provide. We’ll start with the sample data below as the basis for these demonstrations:
Andy focuses mostly on the behavioral differences, where I like TRY_PARSE() a lot, especially because you can control locale for dates and times. When it comes to performance, I’ve found TRY_CAST() and TRY_CONVERT() to be essentially the same performance-wise. Maybe there’s a tiny difference between the two but there’s no guarantee of it.
TRY_PARSE(), however, calls into the .NET CLI for each execution and will be considerably slower. If you’re parsing a few or a few dozen values, you probably won’t notice the difference. If you’re parsing tens of thousands of values (or more), I assure you that you’ll notice the difference.