Logically, there are two cursors performing a typical update operation. One to read and other to write. A read cursor identifies the row which is to be updated and another cursor that performs the write operation to update the rows. In terms of execution, this process is thus divided into two separate parts. To be more specific, first, the write cursor of an updated plan does not affect the second read cursor.

This means that if the data is updated first by the “write” cursor before the data was read by the “read” cursor, then it could be possible to change the position of the row just by updating it. Consequently, read it a second time and it might be different. The means of a key being updated using such an operation in SQL Server, is called the Halloween effect. Let me explain in detail for more clarification. #sql