Halloween Protection The Complete Series

Four Halloween Pumpkins

I have written a four-part series on the Halloween Problem. Some of you will never have heard about this issue. Those that have might associate it only with T-SQL UPDATE queries. In fact, the Halloween Problem affects execution plans for INSERT, UPDATE, DELETE and MERGE statements.

This is a topic I have been meaning to write about properly for years, ever since I read Craig Freedman’s 2008 blog post on the topic, which ended with the cryptic comment:

“…although I’ve used update statements for all of the examples in this post, some insert and delete statements also require Halloween protection, but I’ll save that topic for a future post.”

That future post never materialized, so I thought I would have a go. The four parts of the series are summarized and linked below, I hope you find the material interesting.

Part 1: The Halloween Problem and UPDATE statements

  • The SQL standard and three-phase separation
  • Logical update processing
  • Pipelined execution
  • The Halloween problem
  • Avoiding the problem in UPDATE statements

Part 2 – The Halloween Problem in INSERT and DELETE queries

  • INSERT examples
  • DELETE examples
  • Constraint checking and phase separation

Part 3 – Halloween Problem optimizations for MERGE

  • MERGE contains several optimizations the other DML statements do not
  • Hole-filling with merge join
  • Hole-filling with nested loops
  • Avoiding an extra B-tree navigation
  • Avoiding the join

Part 4 – The Halloween Problem and the Query Optimizer

  • Early optimization approaches
  • The SQL Server optimizer approach
  • The case of the redundant sort
  • HP levels and properties
  • Plan changes for Halloween Protection
  • Non-spool options
  • Row versioning
  • Heaps and forwarded records
  • T-SQL functions