Paul White talks about the Halloween Problem:
Much has been written over the years about understanding and optimizing
SELECT queries, but rather less about data modification. This series looks at an issue that is specific to
MERGE queries – the Halloween Problem.
The phrase “Halloween Problem” was originally coined with reference to a SQL
UPDATE query that was supposed to give a 10% raise to every employee who earned less than $25,000. The problem was that the query kept giving 10% raises until everyone earned at least $25,000.
We will see later on in this series that the underlying issue also applies to
MERGE queries, but for this first entry, it will be helpful to examine the
UPDATE problem in a bit of detail.
This is a classic problem in data management and has led to a good bit of confusion over time about why database updates can perform worse than you’d expect.