Monday, April 2, 2012


 NOLOCK -- If the table is exclusively lock eventhen we can retrieve the rows by using NOLOCK hint along with SQL Server. But there is no guarantee of data consistency.

READPAST It skips the rows which are there inside the transaction and returns the remaining rows from the table.

Example using  Adventureworks database

Select count(*) from Employee -- Returns 290 Rows
Select count(*) from Employee where Title = 'Design Engineer' -- Returns 3 rows

Begin Transaction
Update Employee Set ContactID = ContactID + 1 where Title = 'Design Engineer' -- It will place an exclusive lock placed.

Open other query window, If you are trying to retrieve rows from Employee table, it wont allow you to verify

Select COUNT(*) from Employee -- We need to wait to see the result of this query.

Whereas by using NoLock / ReadPast we can retrieve the data from the Employee Table

Select COUNT(*) from Employee with (READPAST) -- Returns 287 Records, where as it will skip the exclusive locked rows and returns the remaining rows.

Select COUNT(*) from Employees with (NOLOCK) -- Returns all 290 records, but there might be inconsitency at data level.

No comments: