Kenneth Fisher explains the Halloween Problem:
What is The Halloween Problem?
This is a bit more complicated. Let’s say you are trying to give a 10% raise to everyone who makes less than $25k.
Couple of quick notes here. This is a common example because this in fact the problem that exposed the issue. Also, while UPDATEs are probably the easiest way to explain what’s going on, it can affect any type of write.
So back to our update statement. There are several ways this could be implemented. I’m going to use pseudo T-SQL to demonstrate a couple and explain each.
This has certain implications as you can see in the linked Paul White series. These implications typically mean slower performance (e.g., by forcing spooling) but getting rid of a potentially nasty problem.