Halloween effect issue

Problem description:
We are running a very huge SELECT and import the result set inside another table. We found out that we got some duplicates ROWS in the destination table. However it shouldn't occur because each row is unique.

Cause:
We noticed at this time that another query was updating the clustering key of the index.  Updates to the clustering key can cause rows to move within the index.

We were meeting a situation where the physical location of a row within a table changes due to an modification operation.  The result is that the same row may be revisited multiple times at its original location. Reading the data using an index whose key is going to be update is an example of the Halloween effect.

Recommandation:
In most cases it is best to start with a clustered index on every table.

A good candidate for the clustered index key must have the following characteristics:
 - Narrow in size (since repeated in all nonclustered)
 - Unique
 - Incremental
 - Static

As in a cluster index, the data rows are stored in order, based on the cluster index key, this issue occurs because the key is updated. You wouldn't normally expect the key to be frequently update.

Resolution:
To ensure that a row is not updated, encountered again by the same scan, we fix the issue with the hint HOLDLOCK and a plan guide

Links:
- The "Halloween Problem" for XML APIs
- Halloween Protection

Happy Halloween! ;-)

Michel Degremont | Premier Field Engineer - SQL Server Core Engineer |