Paul White talks about the Halloween Problem:
Much has been written over the years about understanding and optimizing
SELECTqueries, but rather less about data modification. This series looks at an issue that is specific to
MERGEqueries – the Halloween Problem.
The phrase “Halloween Problem” was originally coined with reference to a SQL
UPDATEquery 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
MERGEqueries, but for this first entry, it will be helpful to examine the
UPDATEproblem 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.