Press "Enter" to skip to content

Category: T-SQL

Advent of Code Day 3

Published 2023-12-12 by Kevin Feasel

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

Published 2023-12-11 by Kevin Feasel

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

Published 2023-12-11 by Kevin Feasel

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

Published 2023-12-08 by Kevin Feasel

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

Published 2023-12-07 by Kevin Feasel

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

Published 2023-12-05 by Kevin Feasel

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

Set-Based vs Row-Based Code Considerations

Published 2023-12-04 by Kevin Feasel

Kevin Hill explains a concept:

In SQL Server, the terms “set-based” and “row-based” refer to different approaches or styles of writing SQL code to manipulate data. These styles have implications for performance, readability, and the way queries are processed. Let’s explore the differences between set-based and row-based code:

Click through for Kevin’s thoughts. One thing I’d re-emphasize (because Kevin did make this point), especially for people coming to SQL Server from Oracle, is that set-based operations are going to be more efficient about 95-99% of the time than their row-based equivalents. Oracle has a large number of optimizations to make cursor-style code efficient and T-SQL has very few of those, as set-based is the more natural expression of SQL.

One quick example of this is, prior to SQL Server 2012 and its extended support of window functions, the fastest officially supported way to calculate a running total was to build a cursor. The other alternatives, including self-joins, were much less efficient. There was an unsupported but much faster technique that relied on a peculiarity of how SQL Server sorts clustered indexes (the “quirky update” method), but because it relied on internals that could change with any patch, it was a risky maneuver.

Comments closed

Microsoft Fabric and Tabular Editor

Published 2023-11-30 by Kevin Feasel

Johnny Winter is excited:

Why the excitement on my part? Well to take advantage of all the great features in Tabular Editor, you really need to be able to connect and write via XMLA, be that for doing CI/CD pipelines or by making edits directly on the dataset.

What great new features does Tabular Editor unlock that you can’t just do in the online Power BI modelling experience in Fabric… tons!

Read on to see how Tabular Editor plays with Microsoft Fabric.

Comments closed

Indexing for Substring Searches

Published 2023-11-29 by Kevin Feasel

Daniel Hutmacher prepares the bloom filter:

A question from a client got me thinking. Relational databases (at least the ones I know and love) can’t really index for queries that use LIKE queries for a substring of a column value. If you want to search for strings beginning with a given string, a regular rowstore index will have you covered. If you’re looking for entire words or sentences, a full text index might be a good call. But because of the very way indexes work, you’ll never get great performance searching for just arbitrary parts of a string.

So today I’ll put on my lab coat and do a little rocket surgery, just to prove to the world that it can be done.

The suffix tree approach was an interesting one. I’ve also seen people attack this problem using bloom filters (as I alluded to in the link text) and n-grams. A commenter does note n-grams (specifically, tri-grams) as a viable solution as well.

Comments closed