Product has been added to your cart.

Development

Query Exercise: Why Are These 3 Estimates So Wrong?

Last Updated September 18, 2024

Query Exercises

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…

Geographic Sharding is Usually a Pretty Bad Idea.

Last Updated August 17, 2024

Let’s say you built a web app and it became globally popular. You’ve got users scattered across North America, Europe, and Asia. In theory, you could shard your data globally: build 3 sets of SQL Servers, one on each continent, and split up their data. In reality, this usually creates more problems than it solves.…

Query Exercise Answers: Solving the 201 Buckets Problem

Last Updated August 22, 2024

Query Exercises

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

Query Exercises

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

Query Exercise Answers

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

Query Exercises

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

Query Exercise Answers

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

Query Exercises

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

Does Bit Column Order Matter in Tables?

Last Updated June 30, 2024

At the PGConf.dev, where Postgres developers get together and strategize the work they wanna do for the next version, I attended a session where Matthias van de Meent talked about changing the way Postgres stores columns. As of right now (Postgres 17), columns are aligned in 8-bit intervals, so if you create a table with…

The SQL Language is Just a Hurdle You Gotta Overcome.

Last Updated June 25, 2024

I’m not talking just about Microsoft SQL Server specifically here, nor T-SQL. Let’s zoom out a little and think bigger picture for a second: is the SQL language itself a problem? Sometimes when I talk to client developers, they gripe about the antiquated language. The order of a SELECT statement doesn’t make any sense. You…

Query Exercise Answer: Beating ChatGPT at Finding Good Question Times

Last Updated June 15, 2024

Query Exercise Answers

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…

Query Exercise: Beat ChatGPT at Finding Good Question Times

Last Updated July 3, 2024

Query Exercises

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…

Coming in Entity Framework 9: Better Query Parameterization

Last Updated May 28, 2024

Hallelujah. With current versions of Entity Framework, when developers add a mix of parameters and specific values to their query like this: C# async Task<List<Post>> GetPosts(int id) => await context.Posts .Where( e => e.Title == ".NET Blog" && e.Id == id) .ToListAsync(); 12345 async Task<List<Post>> GetPosts(int id) => await context.Posts .Where( e => e.Title == ".NET Blog" &&…

Getting Data Out of an Audit Table: Answers & Discussion

Last Updated May 11, 2024

Query Exercise Answers

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

Who’s Changing the Table? Answers and Discussion

Last Updated July 3, 2024

Query Exercise Answers

Your challenge for this week was to find out who keeps mangling the contents of the AboutMe column in the Stack Overflow database. Conceptually, there are a lot of ways we can track when data changes: Change Tracking, Change Data Capture, temporal tables, auditing, and I’m sure I’m missing more. But for me, there are…

Query Exercise: Who’s Changing the Table?

Last Updated July 3, 2024

Query Exercises

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…

Troubleshooting Mysterious Blocking Caused By sp_getapplock

Last Updated April 24, 2024

Locking, Blocking, and Isolation Levels

I’m kinda weird. I get excited when I’m troubleshooting a SQL Server problem, and I keep hitting walls. I’ll give you an example. A client came to me because they were struggling with sporadic performance problems in Azure SQL DB, and nothing seemed to make sense: sp_BlitzFirst @SinceStartup = 1 showed very clearly that their…

Using ChatGPT for T-SQL Code Reviews

Last Updated April 9, 2024

For this month’s T-SQL Tuesday, Pinal Dave asked us if AI has helped us with our SQL Server jobs. For me, there’s been one instant, clear win: code reviews. I usually keep a browser tab open with ChatGPT 4, and I paste this in as a starting point: You are an experienced, diligent database developer…

Does Your GROUP BY Order Matter?

Last Updated March 1, 2024

Execution Plans

Sometimes when you do GROUP BY, the order of the columns does matter. For example, these two SELECT queries produce different results: Transact-SQL CREATE INDEX Location_DisplayName ON dbo.Users(Location, DisplayName); SELECT TOP 100 Location, DisplayName, COUNT(*) AS Duplicates FROM dbo.Users GROUP BY Location, DisplayName ORDER BY Location, DisplayName; SELECT TOP 100 DisplayName, Location, COUNT(*) AS Duplicates…

Option Recompile is a Magic Turbo Button That Actually Works.

Last Updated March 1, 2024

I didn’t say that – Guy Glantser did. Guy Glantser is an Israeli SQL Server guru with a ton of great presentations on YouTube. I’ve had the privilege of hanging out with him in person a bunch of times over the year, and I’ll always get excited to do it again. He’s not just smart,…