When a set of records are being updated, with the default Isolation Level which is Read Committed, SQL Server makes sure that no one can read them until the transaction is completed. This avoids Dirty Read concurrency issue but it decreases concurrency.
What if you need to read rest of the records that are no locked? Now the question is, how can we instruct to SQL Server to skip locked records and read other records. It makes possible with READPAST hint.
The READPAST hint allows us to read records by ignoring locked record without waiting till incompatible locks to be released. This is NOT similar to READUNCOMMITTED hint because it reads all records including records that are locked.
Here is an example:
Execute the following query in a new window.
USE AdventureWorks2014; GO SELECT * FROM Production.Product; -- This starts a transaction and locks set of records BEGIN TRAN UPDATE Production.Product SET Color = 'Black' WHERE Color IS NULL; -- ROLLBACK TRAN
Now try following codes with another window
-- This wait until locks are released. SELECT * FROM Production.Product -- This returns all records including -- records that are being changed. SELECT * FROM Production.Product WITH (READUNCOMMITTED); -- This returns all records excluding -- records that are being changed. SELECT * FROM Production.Product WITH (READPAST);
See the difference in results. You may find READPAST hint useful with certain scenario.