Why does isolation level 0 (“dirty read”) require that a table have a unique index?
Isolation level 0, introduced in 10.1 and continued through 11.0.x, does not hold any locks while it scans. For this reason, other users may update the page that the reader is scanning, or may even delete that page. When this happens, SQL Server must be able to restart the scan. The only sure way to do that is to remember the key that we used to find this row, and start again from the next key in the index. In an index where keys are not unique, SQL Server might not be able to restart the scan from the right place. Note By using forceindex, it is possible to get a level 0 scan on a non- unique index. Doing this, however, creates the risk that the scan may fail because of a failure to find the new starting place when we detect that the present row has been deleted. For more information and examples, refer to the SQL Server Reference Manual.
