Category: SQL Server

Optimizing Window Functions in Derived Datasets

September 28, 2026

In this post, we’ll look at some interesting behaviour I’ve stumbled across with window functions and derived datasets in SQL Server. I came across a query in a workload that was looking to get the most recent thing per group, a representative example in the Stack Overflow 2010 Database is below. The database is provided…
Keep reading
Filtered Indexes and Plan Parameterization

September 14, 2026

In this post, we’ll look at how filtered indexes in SQL Server work when the query is parameterised on the column of the filtered index. The query The following query in the Stack Overflow 2010 Database, provided under cc-by-sa 4.0 licence from Stack Exchange Data Dump, will find all users with the name John whose…
Keep reading
Reseed Identity – Can it Cause Clashes?

August 31, 2026

I recently had cause to reseed an identity column on a table in SQL Server, which got me wondering “If we reseed an identity column to a value lower than the existing identity values in the column, could it cause a clash?” Because why wouldn’t you wonder if you can cause something to break, whilst…
Keep reading
I Was Today Years Old When…Stored Procedures – Optional String Quotes

August 24, 2026

Today’s post is a quick one and the first in a new series I am calling “I Was Today Years Old When…” where I share simple little nuggets that I stumbled across where I think “How…HOW have I never known this” The series for me will serve as a reminder as to why I love…
Keep reading
sp_executesql and Execution Plans

August 17, 2026

Whilst I would say the actual execution plan is the most useful, estimated execution plans have their place – sometimes you just need to see estimates or a quick verification that a change you have made has had some effect on plan shape. I find them helpful to quickly see if a change made to…
Keep reading
Curse of the Catch-All query

August 10, 2026

The catch-all query – a fairly common pattern that is often seen in SQL Server apps where there is a list of results that can be filtered by one or more criteria. This usually takes the form of a stored procedure that is constructed as below. The example stored procedure is created in the Stack…
Keep reading
In a Query Plan Near You – Stats Loaded For Compilation

August 3, 2026

Something I have been making use of recently is the change in the way statistics that have been used to compile a plan are presented to us as SQL Server users. On a number of occasions I have needed to understand, or at least get some sense of, which statistics SQL Server has used to…
Keep reading
OR predicates and Indexes

August 3, 2026

In this post, we will look at how SQL Server accesses indexes when an OR predicate is used. For this demo, I am going to use the Stack Overflow Database. I am using the large 2024 version provided under cc-by-sa 4.0 licence from Stack Exchange Data Dump and running in compatibility level 160 on a…
Keep reading
KILL and Query Store

July 27, 2026

I love Query Store. I like to think of it as SQL Server’s built-in monitoring tool and I use it a lot to look at query history in terms of run times and IO metrics. One thing I learned about Query Store recently is how it deals with queries that have been killed. Setting up…
Keep reading
Transaction Types

July 20, 2026

This post is just a quick reference post that outlines the three different types of transaction in Microsoft SQL Server. AutoCommit The default. Each statement in a batch works as though it had been individually wrapped in BEGIN TRAN / COMMIT so the following code block: is effectively the same as: This moves us nicely…
Keep reading