NOLOCK and READPAST table hints

When data in a Database is being read or modified, the database engine uses 'Locks' to maintain integrity in the database. Locks basically work by making sure database records involved in a transaction cannot be modified by other transactions until the first transaction has committed, ensuring database consistency.

NOLOCK is also known as "dirty reads". This option directs SQL Server not to issue shared locks and not to honor exclusive locks. So, if this option is specified, it is possible to read an uncommitted transaction. This results in higher concurrency and in lower consistency.

READPAST: Skip locked rows. This option causes a transaction to skip over rows locked by other transactions that would ordinarily appear in the result set, rather than block the transaction waiting for the other transactions to release their locks on these rows. The READPAST lock hint applies only to transactions operating at READ COMMITTED isolation and will read only past row-level locks. Applies only to the SELECT statement.
You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.

Examples:

NOLOCK:

With out any locks specified on the select query, the table 'sales' in schema 'Cars' has 150 records.

SELECT COUNT(*) from Cars.sales

Result : 150

Now, I will insert 2 more rows into this table.

BEGIN TRAN

INSERT INTO Cars.Sales (ID, Make, Model, Price, Color)

VALUES (151, 'Audi', 'R8', 114000.00, 'Red')

INSERT INTO Cars.Sales (ID, Make, Model, Price, Color)

VALUES (152, 'BMW', 'M5', 70000.00, 'Black')

Note: Remember, I did not commit the transaction yet.

Now, I will issue the same select Statement with NOLOCK

SELECT COUNT(*) from Cars.sales (NOLOCK)

RESULT: 152

Now, I do not want these rows to be in the table and I issued a ROLLBACK command..

ROLLBACK TRAN

and all the new rows that I have inserted will be rolled back and if the execute my same SELECT statement, I will get 150 records.

SELECT COUNT(*) from Cars.sales (NOLOCK)

RESULT: 150

So NOLOCK reads uncommited data from the database.

READPAST

In this example, I will Update one record from the 'sales' table ..

BEGIN TRAN

UPDATE Cars.Sales

SET COLOR = 'Red'

WHERE ID = 150

Again, I did not commit the transaction.

Now, If I issue the SELECT statement with READPAST Keyword..

SELECT COUNT(*) FROM Cars.Sales (READPAST)

Result: 149

So as we see, the select statement will ignore the records which are locked in any transaction.