Scripts – sqlsunday.com

T-SQL math patterns

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.

T-SQL template parser

Just for the heck of it, I scratched together a template parser for T-SQL . 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!

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.

Scan hundreds of SQL Saturday raffle tickets. Fast.

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.

Changing the fill factor on multiple tables/indexes

Typically, the advice on fill factor is “if it ain’t broke, don’t fix it”. But occasionally, you’ll find a database or even a server with a crazy default setting that just fills your disk and buffer pool without any real benefit. Here’s a nifty script to rebuild any tables and indexes to a fill factor of 100%.

sqlsunday.com downloads are now on GitHub!

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

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.

T-SQL Tuesday #104: Code you would hate to live without

Everyone has a script, a hack or a checklist they can’t function without. In this edition of T-SQL Tuesday, Bert Wagner challenged us to write about our favorite scripts. This is my take.

Automatically guessing foreign key constraints

With good naming and datatyping conventions, an automated script can help you with the process of creating foreign key constraints across your database, or actually, suggest table relations where you’ve forgotten to implement them.

Finding primary key candidates

Probably one of the most common challenges I see when I do ETL and business intelligence work is analyzing a table (or a file) for possible primary keys. And while a bit of domain knowledge, along with a quick eye and some experience will get you really far, sometimes you may need some computational help just to be sure.

Here are some handy tricks to get you started!