One of my favorite uses for dynamic SQL is to use them to inject query hints. Let's say you've got a stored procedure with a bunch of parameters, including a @StartDate and @EndDate date range. And let's say people usually use a small default date range, like the last week - but every now and then,…
Let's say you're working with a big copy of the Stack Overflow database, and you wanna get all the users, like for a data export. SQL Server doesn't parallelize this query, not even if you use the query hint that asks for a parallel plan:
[crayon-6af3cec78cb29622365598/]
There's a new SQL Server Management Studio update out today.
I'd hold off on it for now.
Your queries won’t tune themselves. My 2027 Live Class Season Pass is $995 (normally $4,395), with live Fundamentals and Mastering classes plus recordings. Register by October 31 to join Fundamentals Week, November 2–6. See the pass.
NOLOCK is bad and you probably shouldn't use it, but every time I mention that publicly, the pushback just keeps coming. I don't know why people so firmly believe that their situation couldn't possibly be affected by bad/random data from NOLOCK.
When you run monitoring queries like sp_BlitzWho and sp_WhoIsActive, you wanna see the program names that are running the queries. It's super useful when you've got multiple apps running on the same servers, or when you've got apps scattered across end user computers.
If you don't set the program names, you'll either end up with empty strings, or your dev tool's default name, like Core .Net SqlClient Data Provider, which doesn't tell you jack.
Wanna play some Texas Hold 'Em style poker and make a pile of Query Bucks? Wanna watch other database people playing?
Fire up SSMS and connect to:
You've probably heard of the setting Max Degree of Parallelism. I hate that name: it should really be called just plain ol' Degrees of Parallelism, and here's why. There are a lot of conflicting opinions out there about how to set it, and Microsoft has official guidance about it in that article above. It's basically set…
Your queries won’t tune themselves. My 2027 Live Class Season Pass is $995 (normally $4,395), with live Fundamentals and Mastering classes plus recordings. Register by October 31 to join Fundamentals Week, November 2–6. See the pass.
Common Table Expressions are awesome because they let SQL Server reorder processing in whatever way it deems to be the most efficient for your current data distribution, on your current version of SQL Server. Default to CTEs.
Your queries won’t tune themselves. My 2027 Live Class Season Pass is $995 (normally $4,395), with live Fundamentals and Mastering classes plus recordings. Register by October 31 to join Fundamentals Week, November 2–6. See the pass.
Over the years, tables - like your waistline - tend to get bigger. We keep tacking on more and more columns, one at a time, in order to handle app needs. It's easier to add "just one more column" than it is to break things off into a whole separate table.
For this month's T-SQL Tuesday, I invited y'all to tell me how you know a query's gonna be bad when you open it. Riccardo Perico and Shane O'Neill says WHILE loops and/or CURSORs are pretty good indicators. I agree, because especially when you're opening code in the year 2026, and someone still hasn't figured this out…
I open it up and the first thing I see is a comment with a changelog showing a long list of dates and author initials.
Oh I know, you think documentation is a good thing, but:
Microsoft SQL Server Management Studio 22.7 added the ability to format your T-SQL queries by right-clicking and clicking Format SQL, or by hitting control-K, control-Q on your keyboard:
You're probably not going to like the default formatting out of the box because you're the kind of person who has strong, passionate feelings and likes to argue about them on the Internet. Good news! The configurations are really flexible. Go into Tools, Options, SQL Formatter:
Way back in December of 2009, Adam Machanic published a blog post (archive) in which he invited database bloggers to participate in a new monthly event he called T-SQL Tuesday. Each month, he (or a blogger he picked) proposed a topic, and on that date, we'd all publish our blog posts at the same time. It was a fun way to get a variety of opinions about a topic.
Normally when I tell people about SQL Server's optimistic concurrency isolation levels, Read Committed Snapshot Isolation (RCSI) and Snapshot Isolation (SI), I have to give them a little speech about how they need to test their queries because the results can change. Recently, though, I was working with a client who was getting the wrong query…
If you do a lot of query tuning, you've probably come across Richie Rump's StatisticsParser.com. Now, it's even easier to use.
Now, it's even easier to use. Download & install it, then run your query with SET STATISTICS IO, TIME ON, and after it finishes, right-click in the query window and click Parse Statistics:
I love me some documentation.
For years, I've pointed folks to the SQL Server 2008 documentation on Forced Parameterization, a really useful tool for reducing plan cache bloat, getting more accurate reusable query plans, and enabling SQL Server 2019 and 2022's Intelligent Query Plan features, many of which rely on the same query text coming in repeatedly over time in order to tune it. That documentation was pretty sparse, though.
At least once a month, I get this question from a client:
We have big data, and we want to save money. We want to move the older data down to some kind of archives that will cost us less.
When there are IF branches or conditional logic, "Edit Query Text" only shows you the branches that actually executed. It's easy to miss whole swaths of code that didn't happen to execute one particular time.
To demo it, let's create a simple stored procedure with an IF branch that sometimes executes, and sometimes doesn't:
The short answer: in the real world, only the first column works. When SQL Server needs data about the second column, it builds its own stats on that column instead (assuming they don't already exist), and uses those two statistics together - but they're not really correlated.
For the longer answer, let's take a large version of the Stack Overflow database, create a two-column index on the Users table, and then view the resulting statistics:
Ever just need a quick visualization of some numbers to get a rough idea of the trend, like this:
Option 1: ask Copilot to write the query for you.
