Category: SQL Server
-
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…
-
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…
-
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…
-
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…
-
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…
-
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…
-
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…
-
This post is the first in a short series about physical join types. What do I mean by physical join types? What we normally consider to be a join – INNER / [LEFT|RIGHT] OUTER / FULL join are logical join types, they are the joins we use to describe the query results in our declarative…
-
In the previous post in this series, we looked at the nested loop operator which we saw was useful when there is a small set data and a second set of sorted data. In this post, we’ll look at what is often considered the workhorse of physical join operators – the hash join. The hash…
-
In these articles, you will see me reference execution plans a lot – talk about them, provide screenshots etc. I assume that the reader has some level of knowledge about what an execution plan is, how to view them and how to understand them. This post is aimed at those readers who do not have…