T-SQL – 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.

How to join overlapping date ranges

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.

Indexing for substring search

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.

Professional driver on closed roads, always wear protection. Your mileage may vary.

The quirky and wonderful self-join optimization

This blog post started as a “what if” contemplation in my head: Suppose you have a reasonably large table with a clustered index and a number of non-clustered indexes. If your WHERE clause filters by multiple columns covered by those non-clustered indexes, could it potentially be faster to rewrite that WHERE clause to use those non-clustered indexes?

The answer might surprise you.

A quick look at SQL Server UTF-8 collations

A client asked me about SQL Server collations, and if they should consider the new UTF8 collations (new since SQL Server 2019). I tried to hide my blank stare of ignorance, and promised them I’d look it up and get back to them.

Not gonna lie, I think UTF and Unicode can be pretty confusing at times, so I did some googling and some testing, and here’s what I found.

I ❤️ QUOTENAME()

An underrated, and perhaps less well-known T-SQL function is QUOTENAME(). If you haven’t heard of it, chances are that it could do wonders for your dynamic SQL scripts.

To quickly recap quoting, consider the following script:

SELECT N'DROP PROCEDURE '+OBJECT_SCHEMA_NAME([object_id])+N'.'+[name]+N';'
FROM sys.procedures
WHERE [name] LIKE N'%test';

What happens if one of your object names contains a space, a quote, an apostrophe, a square bracket, etc? You’ll end up with a syntax error, or even worse, a SQL injection attack (pretty elaborate, but still quite possible). To solve for this, we quote the object names. In SQL Server, you can surround schema and object names with double quotes (if you’ve set QUOTED_IDENTIFIER) or square brackets.

Simple, right?

SELECT N'DROP PROCEDURE ['+OBJECT_SCHEMA_NAME([object_id])+N'].['+[name]+N'];'
FROM sys.procedures
WHERE [name] LIKE N'%test';

But just adding a [ before and a ] after won’t work if your evil user as embedded square brackets or a semicolon in the object name. What if your object name is “Testing [quoting]; test”?

Secure your temporal table history

You may have already discovered a relatively new feature in SQL Server called system-versioned temporal tables. You can have SQL Server set up a history table that keeps track of all the changes made to a table, a bit similar to what business intelligence people would call a “slowly changing dimension”.

CREATE SCHEMA App;

CREATE TABLE App.Customers (
    Company_ID      int IDENTITY(1, 1) NOT NULL,
    CompanyName     nvarchar(250) NOT NULL,
	Email           varchar(250) NOT NULL,
	Valid_From      datetime2(7) GENERATED ALWAYS AS ROW START NOT NULL,
	Valid_To        datetime2(7) GENERATED ALWAYS AS ROW END NOT NULL,
    CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (Company_ID),
	PERIOD FOR SYSTEM_TIME (Valid_From, Valid_To)
) WITH (SYSTEM_VERSIONING=ON);

What happens behind the scenes is that SQL Server creates a separate table that keeps track of previous versions of row changes, along with “from” and “to” timestamps. That way, you can view the contents of the table as it was at any given point in time.

But how to you version the contents of a table, while hiding things like deleted records from prying eyes?

Watch out for Merge Interval with date range Index Seeks

In my last post, I found that DATEDIFF, DATEADD and the other date functions in SQL Server are not as datatype agnostic as the documentation would have you believe. Those functions would perform an implicit datatype conversion to either datetimeoffset or datetime (!), which would noticeably affect the CPU time of a query.

Well, today I was building a query on an indexed date range, and the execution plan contained a Merge Interval operator. Turns out, this operator brings a few unexpected surprises to your query performance. The good news is, it’s a relatively simple fix.

DATEDIFF performs implicit conversions

As I was performance tuning a query, I found that a number of date calculation functions in SQL Server appear to be forcing a conversion of their date parameters to a specific datatype, adding computational work to a query that uses them. In programming terms, it seems that these functions do not have “overloads”, i.e. different code paths depending on the incoming datatype.

So let’s take a closer look at how this manifests itself.

Computing the modulus from very large numbers

… and what of this all has to do with IBAN numbers.

The modulus is the remainder of a division of two integers*. Suppose you divide 12 by 4, the result is 3. But divide 11 by 4, and the result is 2.75. This could also be expressed by saying that 11/4 is 2 with a remainder of 3. Computing that 3 is the work of the modulo operator, which in T-SQL is represented by the % operator.

Let’s explore how to compute the modulus of large numbers in SQL Server, and how this is useful in the real world.