Query Exercises

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,…

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…

“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…

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…

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…

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…

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 –…

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,…

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…

For this week’s Query Exercise, your challenge is to find out who keeps messing up the rows in the Users table. Take any size version of the Stack Overflow database, and the Users table looks like this: People are complaining that from time to time, the contents of the AboutMe column in some – but…

For this week’s query exercise, let’s start with a brief query to get a quick preview of what we’re dealing with: Transact-SQL SELECT TOP 100 Location, COUNT(*) AS Population, AVG(Reputation) AS AvgReputation FROM dbo.Users GROUP BY Location ORDER BY COUNT(*) DESC; 123456 SELECT TOP 100 Location, COUNT(*) AS Population, AVG(Reputation) AS AvgReputationFROM dbo.UsersGROUP BY LocationORDER…

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: Transact-SQL CREATE INDEX DisplayName ON dbo.Users(DisplayName); GO SELECT * FROM dbo.Users WHERE LEN(DisplayName) > 35; 12345 CREATE INDEX DisplayName ON dbo.Users(DisplayName);GOSELECT *  FROM dbo.Users  WHERE LEN(DisplayName) > 35; It has an index, but SQL Server…

Your challenge for this week is to tune a query. Say Stack Overflow has a dashboard that shows the top-ranking users in their most popular location. It’s even got an index to support it: Transact-SQL CREATE INDEX Location ON dbo.Users(Location); GO CREATE OR ALTER PROC dbo.GetTopUsersInTopLocation AS SELECT TOP 200 u.Reputation, u.Id, u.DisplayName, u.WebsiteUrl, u.CreationDate…

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”. What are tags? I’m…

If we’ve gotta take the database down for maintenance – perhaps a version upgrade, perhaps upgrading our own code, maybe scaling up the hardware – when’s the best time to do it? For this week’s query exercise, the business has asked us to identify 3 1-hour periods with the least user activity, in this format:…

For 2024, I’m trying something new: weekly homework challenges! For this week’s query challenge, we’re going to do some data validation. Pop open the Stack Overflow database and check out the CommentCount column on the Posts table: In theory, that’s a fast way to check how many Comments a particular Post has, without querying the…

For 2024, I’m trying something new: weekly homework challenges! For this week, let’s say we’ve decided to implement foreign keys, and we need to find data that’s going to violate our desired keys. We’re going to use the Stack Overflow database, and we’ll focus on these 3 tables: dbo.Users table: with Id column as its…