Handling NoLock via Read Committed Snapshot Isolation (RCSI)

Read Committed Snapshot Isolation (RCSI) is an alternative to NOLOCK in SQL Server. RCSI is a version of the READ COMMITTED isolation level that uses row versioning to provide a consistent view of the data without blocking or causing dirty reads. This eliminates the potential for dirty reads and reduces blocking, which can improve performance.

However, RCSI can increase storage usage (due to row versions stored) and may slow down performance for certain types of queries.

The best approach will depend on the specific requirements of the application and the data being processed.

Syntax for Enabling Read Committed Snapshot Isolation for a Single Query.

SELECT *
FROM dbo.Prod_Units_Base AS PU WITH (READCOMMITTEDLOCK, SNAPSHOT);

This hint causes the query to use RCSI for the duration of the query, even if RCSI is not enabled for the database completely. This can be useful in situations where you only want to enable RCSI for a specific query to avoid dirty reads or blocking, but do not want to enable it for the entire database.