Using NOLOCK and READPAST table hints in SQL Server
To keep SQL Server on tip top performance, all care is taken into considerations when writing application. NOLOCK is one of the common SQL hint to prevent locking on data selection in the context of transaction. Too many lockings will results into record deadlocks. Today come across READPAST hint which is similar to NOLOCK. Below is an extraction of the article found on Tech Republic.com
The READPAST table hint example is very similar to the NOLOCK table hint example. I’ll begin a transaction and update one record in the SalesHistory table.
BEGIN TRANSACTION     UPDATE TOP(1) SalesHistory     SET SalePrice = SalePrice + 1
Because I do not commit or roll back the transaction, the locks that were placed on the record that I updated are still in effect. In a new query editor window, run the following script, which uses READPAST on the SalesHistory table to count the number of records in the table.
SELECT COUNT(*) FROM SalesHistory WITH(READPAST)
My SalesHistory table originally had 300 records in it. The UPDATE statement is currently locking one record in the table. The script above that uses READPAST returns 299 records, which means that because the record I am updating is locked, it is ignored by the READPAST hint.
