Product has been added to your cart.

Development

High CPU time

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

Query Exercises

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

Can AI Rewrite Bad Queries in Seconds? Kinda.

Can AI Rewrite Bad Queries in Seconds? Kinda.

Last Updated January 14, 2025

When I see horrific code, stuff that would take a lot of manual labor to fix, I like to lob it over to an LLM like ChatGPT just to see how it does. For example, on a call with a client, I opened up one of their slowest pieces of code to find that it…

5GB memory grant

I Feel Sorry for Untrained Developers Using Entity Framework.

Last Updated January 10, 2025

Most of the time, I love Entity Framework, and ORMs in general. These tools make it easier for companies to ship applications. Are the apps perfect? Of course not – but they’re good enough to get to market, bring in revenue to pay salaries, and move a company forwards. However, just like any tool, if…

How to Use Github in SSMS v21

Last Updated December 9, 2024

SQL Server Management Studio

SQL Server Management Studio v21 added native Git support, making it easier to use source control natively inside SSMS. This feature is for developers who are already used to working with Github to manage specific object changes as they work, and who are already accustomed to Git terminologies like branches, commits, pushes, and pulls. This…

I'm killing your query.

How to Send Email with SQL Server’s sp_send_dbmail

Last Updated December 9, 2024

So you’re writing T-SQL code and you’ve decided that you want to send emails to customers, employees, or the general public. Perhaps you need to send order updates or low stock notifications. Stop right there. You don’t really wanna do that with SQL Server. Let’s talk about why. It’s not easy to troubleshoot. SQL Server’s…

Updated Training Modules for SQL Server 2019 & 2022

Last Updated November 20, 2024

Execution Plans

At the PASS Summit this month, I presented a 1-day workshop called T-SQL Tuning for SQL Server 2019 & 2022. I just took that material and refactored it into a few different modules for various classes: Mastering Server Tuning – Why Query Monitoring is Hard Now – because I see so many people making bad…

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…

Yes, Cardinality Estimation Keeps Changing After SQL Server 2014.

Last Updated September 26, 2024

Execution Plans

About 10 years ago, Microsoft made changes to the Cardinality Estimator (CE) which caused some problems for SQL Server upgrades. When folks upgraded to SQL Server 2014, they also casually switched their databases’ compatibility level to the latest version, because for years that hadn’t really affected query plans. They just figured they wanted the “latest…

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

Query Exercise Answers

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

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…