T-SQL

In last week’s Query Exercise, I challenged you to play some code golf to generate big spills with tiny T-SQL. Today, I’m going to walk you through my thought process – the initial attempts I tried and failed with, and the discoveries I made along the way, because I think it makes for fun storytelling.…

T-SQL Query Exercise: Generate Big Spills

Last Updated November 5, 2025

When I was testing SQL Server 2025’s new ability to limit TempDB usage with Resource Governor, I wrote a few fun diabolical demos. One of them was to generate giant spills to TempDB, quickly. When you’re looking at an actual (not estimated) query plan, and you see a yellow exclamation point on a sort operator,…

The Query Tuning Trick You Should Use More: Pagination

Last Updated July 15, 2025

When I’m tuning queries, the normal answer is to make the query perform better – either via changing the T-SQL, adding hints, or adding indexes so that the data’s better prepared for the query. However, sometimes when I’m looking at the output of sp_BlitzCache, I scroll across to the Average Rows column and double-check that…

Query Exercise Answers: Returning Routes in the Right Order

Last Updated June 11, 2025

In your most recent Query Exercise challenge, I gave you these two tables: Transact-SQL DROP TABLE IF EXISTS dbo.Stations; CREATE TABLE dbo.Stations (StationId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, StationName VARCHAR(50), StationPhysicalOrder INT); INSERT INTO dbo.Stations (StationName, StationPhysicalOrder) SELECT CHAR(64 + n), n FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11), (12),(13),(14),(15),(16),(17),(18),(19) ) AS Numbers(n); DROP TABLE IF EXISTS dbo.StationRoutingOverride; CREATE…

Query Exercise: Return Routes in the Right Order

Last Updated October 22, 2025

I’mma be honest with you, dear reader: this query exercise stems from a client problem that I couldn’t figure out how to solve in the time constraints that I had, and it drove me crazy. I wanted to put hours into this to figure it out, and I was sure there’d be a fun T-SQL…

Query Exercise Answer: Finding Email Addresses

Last Updated March 20, 2025

For this week’s Query Exercise, your mission had two parts: Do a first pass search through the Users table looking for columns where people had snuck in email addresses where they didn’t belong Write a more high-quality query to identify exactly which rows we needed to handle I wrote the challenge that way because in database…

T-SQL Has Regex in SQL Server 2025. Don’t Get Too Excited.

Last Updated December 10, 2025

SQL Server 2025, T-SQL

Regular expressions are a way of doing complex string searches. They can be really useful, but they have a reputation: they’re hard to write, hard to read, and they’re even harder to troubleshoot. Once you master ’em, though, they come in handy for very specific situations. This post isn’t about their complexity, though. This post…

Query Exercise: Looking for Email Addresses

Last Updated March 15, 2025

“They put their password WHERE?!?” Personally identifiable information (PII) is freakin’ everywhere. When companies first start looking to identify and lock down their data, they think it’s going to be as easy as identifying common columns like EmailAddress, DateOfBirth, SocialSecurityNumber, and so forth. They think, “We’ll just encrypt those columns and we’ll be fine.” Ho…

Should That Be One Update Statement or Multiple?

Last Updated October 16, 2024

Let’s say we have a couple of update statements we need to run every 15 minutes in the Stack Overflow database, and we’ve built indexes to support them: Transact-SQL EXEC DropIndexes @TableName = N'Users'; CREATE INDEX LastAccessDate ON dbo.Users(LastAccessDate); CREATE INDEX AccountId ON dbo.Users(AccountId); GO BEGIN TRAN /* Give reputation points if folks did something…

TRY/CATCH Doesn’t Always Work.

Last Updated August 16, 2024

If you’re using TRY/CATCH to do exception handling in T-SQL, you need to be aware that there are a lot of things it doesn’t catch. Here’s a quick example. Let’s set up two tables – bookmarks, and a process log to track whether our stored proc is working or not: Transact-SQL DROP TABLE IF EXISTS dbo.Bookmarks;…

Query Exercise Answers: Why Are These 3 Estimates So Wrong?

Last Updated August 16, 2024

This week’s Query Exercise challenged you to figure out why these 3 estimates went so badly: Transact-SQL SELECT TOP 250 p.Id, p.Title, COUNT(*) AS VotesCast FROM dbo.Users u INNER JOIN dbo.Posts p ON u.Id = p.OwnerUserId INNER JOIN dbo.Votes v ON p.Id = v.PostId WHERE u.Location = 'Reading, United Kingdom' GROUP BY p.Id, p.Title ORDER…

Query Exercise: Why Are These 3 Estimates So Wrong?

Last Updated September 18, 2024

Our prior Query Exercise introduced SQL Server’s 201 buckets problem: its inability to accurately estimate rows for more than 201 outliers in a table. I followed up with a solution using filtered statistics to help with the next 200 outliers, and I talked about how that’s really overkill for the simple problem we were facing…

Query Exercise Answers: Solving the 201 Buckets Problem

Last Updated August 22, 2024

In this week’s Query Exercise challenge, I explained SQL Server’s 201 buckets problem. SQL Server’s statistics only handle up to ~201 outliers, which means that outliers ~202-300 get wildly inaccurate estimates. In our example, I had an index on Location and perfectly accurate statistics, but even still, this query gets bad estimates because Lithuania is…

Query Exercise: Solving The 201 Buckets Problem

Last Updated August 28, 2024

When you run a query, SQL Server needs to estimate the number of matching rows it’ll find – so that it can decide which indexes to use, whether to go parallel, how much memory to grant, and more. For example, take any Stack Overflow database, and let’s say I have an index on Location, and…

Query Exercise Answer: What Makes SELECT TOP 1 or SELECT MAX Different?

Last Updated August 8, 2024

This Query Exercise was very different: I didn’t ask you to solve a particular problem. I pointed out that I’ve heard advice that SELECT MAX is faster than SELECT TOP 1, and that’s not quite true. I asked you to find factors that would cause these two queries to get different execution plans: Transact-SQL SELECT TOP…

Query Exercise: What Makes SELECT TOP 1 or SELECT MAX Different?

Last Updated August 16, 2024

These two queries both get the same answer from the Stack Overflow database: Transact-SQL SELECT TOP 1 LastAccessDate FROM dbo.Users ORDER BY LastAccessDate DESC; SELECT MAX(LastAccessDate) FROM dbo.Users; 123456 SELECT TOP 1 LastAccessDate FROM dbo.Users ORDER BY LastAccessDate DESC; SELECT MAX(LastAccessDate) FROM dbo.Users; But do they go about their work the same way? As it turns out, no –…

Query Exercise Answer: Fixing a Slow Computed Column

Last Updated July 18, 2024

In last week’s Query Exercise, we added a user-defined function to the Users table to check whether their WebsiteUrl was valid or not. I noted that even with an index on Reputation, SQL Server 2022 simply ignored the index, did a table scan, and spent 2 minutes of time calling the user-defined function on a…

Query Exercise: Fix This Computed Column.

Last Updated July 23, 2024

Take any size of the Stack Overflow database and check out the WebsiteUrl column of the Users table: Sometimes it’s null, sometimes it’s an empty string, sometimes it’s populated but the URL isn’t valid. Let’s say that along the way, someone decided to ask ChatGPT to build a function to check for valid website URLs,…

The SQL Language is Just a Hurdle You Gotta Overcome.

Last Updated June 25, 2024