Category: SQL Server

Cross Database Deferred Name Resolution

July 13, 2026

I have been aware of temporary stored procedures in Microsoft SQL Server for a long time but never really had cause to use them, however, recently a need arose. I was testing what effect some index changes would have on a particular stored procedure. The testing was on a non-production server, and when I went…
Keep reading
#tsql2sday #200

July 7, 2026

This post is my first T-SQL Tuesday post since starting the blog a couple of months back. T-SQL Tuesday was originally set up by Adam Machanic, original creator of the essential sp_whoisactive stored procedure. The concept is that once a month, a different host puts out an invitation to the community for bloggers to write…
Keep reading
SQL Server – See What’s Executing Now With DMVs

July 6, 2026

In SQL Server, there are a few DMVs that tell us “What is running now”. This is built into SSMS as Activity Monitor, but I’ve never met anyone who actually finds that method useful, there’s also sp_who and sp_who2, but they are dated and limited in what they tell you. Instead, the more robust way…
Keep reading
Schema Prefixing is Important

June 29, 2026

In this post, we’ll look at why it is important to prefix objects with their schema and look at an example where not prefixing objects with schemas can cause unexpected results. For this post, we’ll use the Northwind database, made available by Microsoft at the Northwind database repository on GitHub. I have made some modifications…
Keep reading
SQL Server – Working Smarter, Not Harder (Again)

June 22, 2026

In the last post we talked about the concept of creative laziness and how SQL Server’s query optimizer notices when the user has asked for something that requires no actual work due to a logical contradiction. In this post, we’ll look at another example of where the optimizer works smarter not harder and can reduce…
Keep reading
SQL Server – Working Smarter, Not Harder

June 22, 2026

Back in my university days, a lecturer of mine would say how programmers are experts in “creative laziness” He was referring to practices such as using methods and classes in C# – constructs that mean as a developer, you follow the DRY (Don’t Repeat Yourself) ethos – you are saving yourself time by writing things…
Keep reading
SQL Server Version Store Growing

June 15, 2026

Enabling Read Committed Snapshot Isolation (RCSI) or snapshot isolation on a database changes the locking behaviour from that of SQL Server’s default, Read Committed isolation. With RCSI or Snapshot Isolation enabled, readers no longer block writers and vice versa, though writers can still block writers. This is implemented by creating and using a version store…
Keep reading
Triggers use the version store

June 8, 2026

Here’s one I stumbled across recently – I was monitoring tempdb space and could see some version store usage on a database that did not have snapshot or Read Committed Snapshot Isolation enabled, I was puzzled by this, but eventually found the answer. What is the Version Store? As a quick primer, the version store…
Keep reading
Don’t Miss a Beat: How Blocking Can Cause Unintended GETDATE() Behaviour

June 1, 2026

I was working on some SQL Server code recently which needed to check if an event that is logged to a table has completed and perform an action if it had. The way I looked to implement this was to run an agent job every 5 minutes to see if an event had occurred in…
Keep reading
Physical Joins – Merge Join

May 25, 2026

In this series, we’ve been looking at the internals of physical joins – the operations SQL Server does behind the scenes to satisfy the logical joins written in our declarative T-SQL. So far, we’ve looked at nested loop joins and hash joins, and in this final post, we’ll look at the merge join. The Algorithm…
Keep reading