The topic this month (from Reitse Eskens – @2meterDBA) is about “Fun T-SQL”. Now, I remember writing T-SQL to create palindromes once. It’s over at https://www.itprotoday.com/sql-server/palindromes-puzzle-solutions, and dates back to January 2007, when I’d been an MVP for less than a year. These kinds of challenges are fun puzzles.
Back in my uni days I remember a Prolog assignment to solve “each letter represents a number” puzzles, and my solution being slow. Years later I tried it again and it worked out just fine, but by then the due date was in the past and they weren’t prepared to change my grade.
While these kinds of things can be fun (more so when there aren’t uni grades dependent on the solution), there are also times that it can be fun to rewrite some code in a way that is more intuitive, or that feels clever in a profoundly simple way. Like calculating someone’s age by subtracting the dates in yyyyMMdd and dividing by 10000. It’s definitely simple but also very tricky, and there’s something neat about it. Various techniques with windowing functions also feel like this, or using geometry indexes when a 2D index would be good (like indexing intervals). I enjoyed coming up with a way of tuning spatial queries by storing low-res inner and outer bounds some years back, although I can’t say I’ve had much call for it recently. It’s not like the need for spatial queries has disappeared, it just doesn’t seem to be as hot a topic now compared to ten years ago.
I think the times I find T-SQL the most fun is when I need to leave a comment to explain why I’m doing something a particular way. One of those times that a method I’ve used feels unintuitive at first, but is really significant from a performance perspective.
My favourite of these is the “inverse predicate” method I use for making sure that indexes can be used effectively. It’s part of my stuff on sargability. The idea is that if you have a query that involves multiple tables, and the “ideal plan” depends on which table is being filtered more selectively, you might want the sargability to apply both ways. So rather than just writing (for example): ON t1.DateCol = DATEADD(day, 1, t2.DateCol), you might want to write: ON t2.DateCol = DATEADD(day, -1, t1.DateCol).
The bit where it becomes “fun” is when you realise that you want both. Because you know that someone will come back to that code and say “Hang on, these two lines mean the same, we don’t need both”. And they’d be right. Except that sometimes they’d need one and sometimes they’d need the other. I like it when it clicks for someone and they see the reason behind what seems like madness.
Puzzles can be fun, but often the most fun is finding that special method for making something work, that required thinking outside the box. That way, even ordinary problems can become fun puzzles.
@robfarley.com@bluesky (previously @rob_farley@twitter)
