A technical SQL Server blog from New Zealand. See also my articles on SQLperformance.com

Halloween Protection – The Complete Series

I have just published a four-part series for SQLPerformance.com 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.

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.