Halloween Protection – The Complete Series

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

As always, I appreciate your comments and feedback.

© Paul White
email: SQLkiwi@gmail.com
twitter: @SQL_Kiwi