Scripts

T-SQL math patterns

/ Daniel Hutmacher / 4 Comments

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.

If there are any other functions you’d like to add, feel free to add a comment to this post, or, by all means, a pull request to the repo.

Warning: Math ahead

T-SQL template parser

/ Daniel Hutmacher / 1 Comment

Just for the heck of it, I scratched together a template parser for T-SQL GitHub. The usage of this function is similar to the STRING_SPLIT() function, except instead of splitting a string by a delimiter character, we want to split a string according to a defined template.

SELECT *
FROM dbo.Template_Split(
    'Wingtip Toys (Bethel Acres, OK)',
    '% (%, %)'
);

… will generate the following output:

Notice how the “%” wildcard character denotes how the string is split. Unlike the fancy stuff you can do with regular expressions, T-SQL wildcards don’t allow you to define capture groups, so this function is unfortunately constrained to just using “%”. I hope it will still come in handy to someone out there.

That’s it, that’s the post. Enjoy!

Anonymize your dev databases!

/ Daniel Hutmacher / 6 Comments

I’ve been working on a little gadget for a while now, and today I finally got around to completing it and so now I’ve published it for everyone to try out. It’s a web API (wait, wait, don’t go away – it’s for database people!) that creates a randomized list of names, addresses, etc.

In this post, I’ll show you how easy it is to use this service to anonymize a development or test database so you don’t have all that personally identifiable information floating around.

Continue reading →

Scan hundreds of SQL Saturday raffle tickets. Fast.

/ Daniel Hutmacher / Leave a comment

If you’re an organizer, sponsor and/or an exhibitor at a SQL Saturday event, you often collect raffle tickets from attendees. In exchange for giving you their contact information, they have the opportunity to win cool raffle prizes.

Here’s the thing, though. Raffle tickets can be a bit painful to scan on your mobile phone. You point the phone at the QR code, then click the URL that opens up the browser, after which the SQL Saturday web service takes another second or two (or three) to load. This is fine when you have just a few tickets, but it can be mindnumbing if you have hundreds.

What can I say, I’m lazy.

Continue reading →

sqlsunday.com downloads are now on GitHub!

/ Daniel Hutmacher / Leave a comment

Regular readers of my blog will know that I occasionally share some useful scripts on my Downloads page. And even though I update some of those scripts regularly when new versions of SQL Server come out, or if I run into a bug feature, there really hasn’t been a practical way for readers to subscribe to those updates or to contribute with good ideas.

I recently attended the annual PASS Summit conference in Seattle, and as part of my personal goal to try to learn new (and scary) things, I took a precon on working with Git.

So as of now, a bunch of downloads are available on GitHub (which is, really, a much better place to host scripts than a shared Dropbox link). You can download them as usual, and if you want, you can add your improvements and send me a pull request. I know I’ve received a ton of good ideas and suggestions over the years, but more often than not, I haven’t had the proper environment to test those changes in, or I just haven’t had the time to dig into my old code.

But now you can:

The SQL Server Calendar project

/ Daniel Hutmacher / 13 Comments

I’m the type of developer that invents wheels. Yes, every wheel I design is unique in its own way, and hand-crafted for a specific purpose. And so it has also been with calendar dimensions (typically when I do data warehousing work).

This got me thinking – why not design the mother of all calendar dimensions? One that includes every conceivable calendar and property that I and others could use and re-use. One that could save me a ton of coding, and lessen the burden of having to validate it each and every time?

And that’s how I got started designing my one calendar script to rule them all.

Continue reading →