T-SQL and Database Development

Writing, reviewing, debugging, and maintaining T-SQL and database code.

478 associated posts232 primary posts

Performance Tuning

How to Query JSON Data Quickly in SQL Server, Part 1: Pre-2025

November 11, 2025

Before SQL Server 2025, if you want to store JSON data in Microsoft SQL Server or Azure SQL DB, and you want fast queries, the easiest way is to:

Store the data in an NVARCHAR(MAX) column (because the native JSON datatype didn't arrive until SQL Server 2025)
Add a computed column for the specific JSON keys we'll want to query quickly
Index those keys
Query it using the JSON_VALUE function

Read more about How to Query JSON Data Quickly in SQL Server, Part 1: Pre-2025 12 comments — Join the discussion
T-SQL & Development

The Query Tuning Trick You Should Use More: Pagination

July 29, 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 the query's actually returning a reasonable number of rows out in the wild.

Read more about The Query Tuning Trick You Should Use More: Pagination 6 comments — Join the discussion

How SQL Server 2025’s Optional Parameter Plan Optimization Works

May 22, 2025

About three years ago, SQL Server 2022 introduced Parameter-Sensitive Plan Optimization (PSPO). At the time, I explained that it didn't work particularly well, and went so far as to pronounce PSPO in a rather unflattering way. I wouldn't suggest that anyone turn it off - it's fine, just fine - but it isn't powerful enough, and poses serious challenges for monitoring and plan cache analysis.

Read more about How SQL Server 2025’s Optional Parameter Plan Optimization Works 15 comments — Join the discussion
T-SQL & Development

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

March 19, 2025

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 is about Azure SQL DB & SQL Server 2025's regex performance.

Read more about T-SQL Has Regex in SQL Server 2025. Don’t Get Too Excited. 23 comments — Join the discussion
Performance Tuning

Query Exercise: Looking for Email Addresses

March 18, 2025

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."

Read more about Query Exercise: Looking for Email Addresses 12 comments — Join the discussion