Press "Enter" to skip to content

Category: T-SQL

Advent of Code Day 4

Kevin Wilkie continues a Quixotic quest. Part 1 sets up the story:

Now we get to work with a set of numbers that our elf played and a set of winning numbers for each card. This sounds like something that SQL is meant for!

As always, we load the fun input data provided by the AoC group into our database. I actually loaded it in as 2 columns – Card and String. I’m just not a fan of throwing all of our data into one column of a table and letting it all get sorted out later.

Part 2 wraps it up:

With part 1, we just had to figure out how many times each of the winners showed up. With part 2 though, we have to jump through several hoops since we now have to determine how many cards we will end up with if we win the next series of cards for every match.

I slimmed down the table that I’m working with to only 2 columns – yes, in the real world, I would have used a view, but today was not that day.

By the way, as soon as I saw OverlyLongNamesThatNoOneCanTypeWithoutUsingIntellisense I wondered when Kevin got access to my code base. I have, on a few occasions, created punishment names, names so long that they punished the people who had to type or track them. In fairness, it wasn’t just a fit of pique, though pique was a common factor in all of those situations.

Comments closed

Batch Changes in T-SQL

Erik Darling doesn’t update all the rows at once like some barbarian:

The goal of batching modifications is to accomplish large numbers of row modifications with exacerbating locking problems, and being kinder to your server’s transaction log.

There is generally an appreciable difference in transaction time in modifying 1,000 rows and modifying 10,000,000 rows. Go try it, if you don’t believe me.

Click through for more information on good ways to set up batching, including the use of the highly underrated OUTPUT ... INTO ... clause on DML statements.

Comments closed

Fun with WAITFOR

Aaron Bertrand plays red light, green light:

WAITFOR is a very useful command to prevent further operations from occurring until a certain amount of time has passed (WAITFOR DELAY) or until the clock strikes a specific time (WAITFOR TIME). These commands are covered quite well (including pros and cons) in a previous tip, “SQL WAITFOR Command to Delay SQL Code Execution.”

WAITFOR DELAY is also commonly used to “let SQL Server breathe.” For example, when performing some operations in a loop, on a resource-constrained system, an artificial delay can potentially allow for more concurrency, even if it makes the intensive operation take longer.

But these commands can also be used to synchronize multiple query windows for local testing and debugging, and I thought I would share a recent example.

Click through for some of the ways you can use WAITFOR in your scripts.

Comments closed

Advent of Code Day 3

Kevin Wilkie lives the struggle. Check out part one:

Then we do something very similar to what we did in Walking Through the Advent of Code Day 2 – find the digits, and the remaining text, and we’re home free!

Notice, though, that the position we grab is found at the very end. We just find the string in the line and show where it is. Except, that doesn’t always work.

And then there’s part 2:

Hopefully, by now, you’ve read my post Walking Through the Advent of Code Day 3. After looking through it, you’re probably thinking… “What have I gotten myself into? Is SQL the way to go with all of this? Can I back out now and learn a programming language that AoC can be done in “easily”?”

Click through to experience the pain or at least have a little bit of Schadenfreude in your life.

Comments closed

Random Number Generation in T-SQL

Andy Yun has a method:

This is a quick blog to “document” a T-SQL technique for generating random numbers. I’ve been using this for years, but don’t use it frequently enough to have it fully memorized. So whenever I do need it, I must constantly have to go look up whenever I need to use it.

Click through for Andy’s method. This will generate random numbers based on a uniform distribution: the likelihood of getting any value in the range is equal. If you want to build out some data that approximates a normal distribution, I have a blog post for that.

Comments closed

Formatting Your Stored Procedure Code

Erik Darling takes a tour of the land mine garden:

When you think about formatting code, you probably think primarily of organizing a single query so that it’s inoffensive to civilized society.

But you should also apply this to your code as a whole, too. People who use words wrong will call this “holistic”.

I won’t get too deep into level of agreement here (probably about 60-70% of Erik’s list I can agree on), but I do argue that the best standards are the ones your team agrees on. It’s frustrating seeing hairball messes of T-SQL. Especially when developers’ non-SQL code looks a lot neater.

Comments closed

Continuing the Advent of Code

Kevin Wilkie has been busy. Here’s Day 1 Part 2:

Today, I want to review part 2 of Day 1 of the Advent of Code series. Hopefully, everyone was able to complete part 1 with no troubles, or at least understood what I did to get there.

For part 2, they added a slight wrinkle to the part 1 puzzle. They spell out the numbers into actual words! How do you find them as well as find the numbers? Well, my friend, let’s go through that process, shall we?

After that is Day 2 Part 1:

On day 2, we are asked to gather data from a series of games and to see which of those are possible given a specific number of dice for a few colors. Fun times!

And then there’s Day 2 Part 2:

Today, we’ll be working on the next in the series using the data and processes that we found yesterday in Day 2 Part 1 – found here.

Thankfully, we were smart when we began working through the data and we have the data for each of our dice in separate tables, so breaking the data apart has definitely paid off! Now we can do just a little bit of work with the data from yesterday and we’ll be ready to give the results!

Comments closed

Joining on Overlapping Date Ranges in T-SQL

Daniel Hutmacher crosses the streams:

You can get into a situation where you have two tables with values associated with date ranges. What’s worse, those date ranges don’t necessarily have to align, which can make joining them a seemingly complex task, but it is surprisingly simple when you learn how to think of overlapping date ranges, along with this relatively simple T-SQL join pattern.

This problem gets even more challenging if you have the possibility of multiple overlaps and you want to find the combination with the biggest overlap for each individual item.

Comments closed

Finding the First and Last Number of a String in SQL and Excel

Kevin Wilkie does some soul searching, or at least string searching:

To enjoy these puzzles, you will need to go to the Official Advent of Code website, sign up for their leaderboards and whatnot if you choose to, and then continue to 2023 and Day 1. Today, we’ll start with Day 1 – since it is the first of our programming puzzles and work our way up from there…

We’re asked – given a string – to find the first (and last) number in that string. We are then to concatenate them, add them all up, and provide the result. It should be pretty simple, but let’s see…

Click through for Kevin’s two answers.

Comments closed