Search code examples
sqlsql-servert-sqlnolock

Will SQL Server NOLOCK hint return partially written rows?

First of all I'm not asking if NOLOCK should or should not be used. Let's get past that.

I guess the question comes down to how sql server writes data? Is an entire row written at once or does it write it a column at a time?

I'm asking because the NOLOCK hint is being considered. A dirty read is fine as long as the entire row is returned (or not returned). Partially written rows are not acceptable.

Solution

  • No. Data modification operations like inserts, updates and deletes are protected by low level physical Latches. All data access operations, including lock-free SELECT, are obliged to conform to the latching protocol. The result is that partial writes are never seen by any reader.