Hands-on T-SQL query exercises and their answer or discussion posts.
31 associated posts31 primary posts
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.
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, that means:
In your most recent Query Exercise challenge, I gave you these two tables:
[crayon-6a6eb8207561d552864764/]
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 way to do it.
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
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."
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 row-by-row basis.
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.
For this week's Query Exercise, I asked you to write a better query than ChatGPT wrote. Your goal was to find the best days and times to post questions on Stack Overflow.
I found it interesting that a lot of the initial answers focused on the times when there were the most questions, or which questions were the most highly upvoted. For me, the best time to post a question is when you have the highest likelihood of getting the right answer, quickly.
What are the best days of the week and times of the day to post a question at StackOverflow.com? It seems like a simple question, but it's surprisingly nuanced. I asked ChatGPT's latest version, 4o, and its answer made me laugh out loud. First off, the T-SQL is terrible: it creates a completely unnecessary temp…
In last week's post, I gave you a trigger that populated a history table with all changes to the Users.AboutMe column. It was your job to write a T-SQL query that turned these Users_Changes audit table rows:
Into the full before & after data for each change, like the earlier query from the blog post:
This week's query exercise asked you to find two kinds of locations in the Stack Overflow database:
Locations populated with users who seem to be really helpful, meaning, they write really good answers
Locations where people seem to need the most help, meaning, they ask a lot of questions, but they do not seem to be answering those of their neighbors
For this week's query exercise, let's start with a brief query to get a quick preview of what we're dealing with:
[crayon-6a6eb820773ec738748551/]
That query has a few problems, but hold that thought for a moment. (You're going to have to solve those problems, but I just wanted to show you the sample data at first to give you a rough idea of what we're dealing with.)
Your query exercise for this week was to write a query to find users created in the last 90 days, with a reputation higher than 50 points, from highest reputation to lowest. Because everyone's Stack Overflow database might be slightly different, we had to start by finding the "end date" for our query. I'm working with the 2018-06 export that I use in my training classes, so here's my end date:
For this week's Query Exercise, we're working with the Stack Overflow database, and our business users have asked us to find the new superstars. They're looking for the top 1000 users who were created in the last 90 days, who have a reputation higher than 50 points, from highest reputation to lowest. In your Stack…
Our developers have come to us with a problem query that isn't as fast as they'd like. Using any Stack Overflow database:
[crayon-6a6eb82078033706726743/]
It has an index, but SQL Server refuses to use that index in the execution plan:
If we force the index with a query hint, we do indeed get dramatically lower logical reads. In my particular database's case, the clustered index scan is 141,573 logical reads - but scanning the DisplayName index alone is just 38,641 logical reads.
In the last Query Exercise discussion, we hit a performance wall when we were trying to quickly find questions with a specific tag. We threw up our hands and said it's time to make database changes, but we had a few restrictions: We have a lot of existing code that uses the Posts.Tags column That…
Your query exercise was to take this Stack Overflow query to find the top-voted questions for any given tag:
[crayon-6a6eb82078ad9832551705/]
That's currently using this index in its execution plan:
[crayon-6a6eb82078ade899811478/]
And answer 3 questions:
What kinds of tags will perform worse than others for this query?
Could you change the query to perform better?
Could you change the indexes to perform better, without changing the table structure?
For this week's Query Exercise, we're going to start with an existing query that has performance problems from time to time, depending on how it's used.
This query's goal is to find the top-voted Stack Overflow questions for any given tag - for example, here are the top-voted questions tagged "sql-server".
Your Query Exercise was to find the best time to do database maintenance by querying the Users table, looking for a one-hour window with the lowest number of created users. We kept this one pretty simple by looking at the data in just one table, and we didn't hassle with time zones. We just wanted the 3 lowest-load hours, in this format:
