THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Page Free Space

See also my SQL Server technical articles on

Halloween Protection – The Complete Series


I have just published a four-part series for on the Halloween Problem. Some of you will never have heard of this issue, and 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, sadly, 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

As always, I appreciate your comments and feedback.

Paul White


Published Thursday, February 21, 2013 12:29 PM by Paul White

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



john said:

Good article, makes our SQL skills improve fast.

February 21, 2013 3:54 AM

JOE said:


March 9, 2013 6:31 AM

Alfred said:

awesome!! thanks for your sharing. From Taipei, Taiwan

May 19, 2013 10:24 AM

Joe Celko said:

Nice work! Before CASE expressions, the reason given for cursors was an updating problem for book prices in the old Sybase sample database. Reduce all expensive books (>= $25) by 10% and raise the cheap books by 10%. (make up your own numbers) If you did this with two UPDATEs, one for the cheap books and one for expensive books, the books on cusp go up then down, down then up in price. $25.00 => 22.50, then back to $22.50 => $24.75. This was the argument for a cursor that would visit each book once. Today, UPDATE Books Set price = price *CASE WHEN price >= 25.00 THEN 0.90 ELSE 1.10 END; does the job.

December 5, 2013 1:34 PM

Paul White said:

Thank you, Joe!

December 5, 2013 8:41 PM

Leave a Comment

Privacy Statement