With(NoLock) vs With(ReadPast)

WITH (NOLOCK) and WITH (READPAST) are SQL Server table hints that manage concurrency differently — NOLOCK reads uncommitted (dirty) data, while READPAST skips locked rows, avoiding dirty reads but potentially missing data.

With(NoLock) v/s With(ReadPast)

What is WITH (NOLOCK)?

• WITH (NOLOCK) is a table hint used in SQL Server to read data without acquiring shared locks and without honoring exclusive locks held by other transactions.
• It also ignores exclusive locks placed by other transactions.
• your SELECT statement will not wait if another transaction is modifying the same data.
• It allows reading data even if it's not yet committed — also known as a dirty read.

What is WITH (READPAST)?

• WITH (READPAST) is a table hint used in SQL Server to read data while skipping rows that are currently locked by other transactions.
• It avoids blocking by ignoring locked rows, instead of waiting for them to become available.
• Your SELECT statement will not wait if another transaction is holding a lock on some rows — it will simply skip those rows.
• It allows reading only committed data, and does not return dirty or uncommitted rows.

Conclusion

In conclusion, WITH (NOLOCK) improves read performance by avoiding locks but risks dirty or inconsistent data. Use it carefully—only when performance matters more than absolute accuracy, like in non-critical reporting queries.