Recently I got a fun question about an “upsert” pattern as a “Dear SQL DBA” question. The question is about TSQL, so it lent itself to being answered in a blog post where I can show repro code and screenshots. Here’s the Scenario in the Anonymized Question We have a lookup table, which is the parent table in a foreign key relationship with a child table. The child table has lots more rows. When data comes in, we need to: Check the Parent Table (the lookup table) to see if the ParentValue is already present. If ParentValue is not present, insert a row for the new ParentValue into the Parent Table. This happens very rarely, but we need to check for it. Then insert a row into the Child Table, using the ParentId. The Problem: Blocking against the Parent Table When run under a few sessions, locking and blocking issues were creeping…

SQL Server offers two flavors of optimistic locking for traditional disk-based tables: Read Committed Snapshot Isolation (RCSI), and Snapshot Isolation. They are each great tools to reduce blocking and make applications faster, particularly in databases that have lots of tiny reads and writes that need to be quick, or mixed-use patterns (lots of little reads and writes + larger reporting queries). Both isolation levels are available in SQL Server Standard Edition and Web Edition as well as Enterprise. That’s right, they’re cheap and easy. Each of them are controlled by database level settings, and default to disabled for new user databases when you install SQL Server and leave the default settings on the model database. When should you pick one or the other? And when might you enable both? Cheat Sheet for Snapshot and RCSI Lots of details are below, but you don’t have time to read all that. Here’s what you want to know:…