GilaMonster (10/31/2012)
Andrew Diniz (10/31/2012)
After all, it is possible to observe 'Halloween Protection' in DELETE and INSERT plans too :hehe:No, the Halloween problem is exclusively for updates. You can't insert or delete a row multiple times, you can an update and that's the problem. You may see spools in insert or delete plans, but they are not there for halloween protection.
This is a common misconception. Internally, all data-changing operations are referred to as updates. This includes insert, delete, update, and merge. Confusing for SQL people perhaps, but that's the way it is. This usage in Ian's post seems to have misled Steve today because Halloween Protection is most definitely required (and a strategy computed) for update plans (I/U/D/M) in general, not just updates. There were several bugs (in SQL Server 7/2000 I think) that related to incorrect HP for inserts and deletes with self-joins, for example.
Craig Freedman states this explicitly at the end of the following entry:
http://blogs.msdn.com/b/craigfr/archive/2008/02/27/halloween-protection.aspx
edit: Adding KB http://support.microsoft.com/kb/810026
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi