Effect of NOLOCK hint in SELECT statements

by

1) Yes, a select with NOLOCK will complete faster than a normal select.

2) Yes, a select with NOLOCK will allow other queries against the effected table to complete faster than a normal select.

Why would this be?

NOLOCK typically (depending on your DB engine) means give me your data, and I don’t care what state it is in, and don’t bother holding it still while you read from it. It is all at once faster, less resource-intensive, and very very dangerous.

You should be warned to never do an update from or perform anything system critical, or where absolute correctness is required using data that originated from a NOLOCK read. It is absolutely possible that this data contains rows that were deleted during the query’s run or that have been deleted in other sessions that have yet to be finalized. It is possible that this data includes rows that have been partially updated. It is possible that this data contains records that violate foreign key constraints. It is possible that this data excludes rows that have been added to the table but have yet to be committed.

You really have no way to know what the state of the data is.

If you’re trying to get things like a Row Count or other summary data where some margin of error is acceptable, then NOLOCK is a good way to boost performance for these queries and avoid having them negatively impact database performance.

Always use the NOLOCK hint with great caution and treat any data it returns suspiciously.

More Related Contents:

  1. Only inserting a row if it’s not already there
  2. SQL Server – How to lock a table until a stored procedure finishes
  3. How to find out what is locking my tables?
  4. Can anyone help me to get the query to retrieve data like below [closed]
  5. NOT IN vs NOT EXISTS
  6. Simple way to transpose columns and rows in SQL?
  7. Optimal way to concatenate/aggregate strings
  8. How can I delete using INNER JOIN with SQL Server?
  9. Maximum size for a SQL Server Query? IN clause? Is there a Better Approach [duplicate]
  10. Most efficient way in SQL Server to get date from date+time?
  11. Convert varchar into datetime in SQL Server
  12. Using the result of an expression (e.g. Function call) in a stored procedure parameter list?
  13. When no ‘Order by’ is specified, what order does a query choose for your record set?
  14. Recursive query in SQL Server
  15. How do I create a foreign key in SQL Server?
  16. How to create table using select query in SQL Server?
  17. How do I group on continuous ranges
  18. join comma delimited data column
  19. conversion of a varchar data type to a datetime data type resulted in an out-of-range value
  20. How do I pass a list as a parameter in a stored procedure?
  21. Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
  22. Can you have if-then-else logic in SQL? [duplicate]
  23. How to list files inside a folder with SQL Server
  24. SQL – Call Stored Procedure for each record
  25. Group All Related Records in Many to Many Relationship, SQL graph connected components
  26. Should I use the CASCADE DELETE rule? [duplicate]
  27. Dynamic order direction
  28. Why is RAND() not producing random numbers?
  29. SQL, How to Concatenate results?
  30. Should every SQL Server foreign key have a matching index? [closed]

Leave a Comment