What is the practical used of nolock in sql server

Question

I was going around some document in msdn and it said that "No shared locks are issued to prevent other transactions from modifying data read by the current transaction".

So in lay man term(i.e mine) this would cause the problem of dirty read. Which is too dangerous, if so then why to used it?

Does anybody knows the practical scenario where it would be used.

Answer 1

In our case (previous job) we used this for getting ballpark numbers. So for example a table that holds millions of e-mails sent in a given day, if at 5:00 we want to see "where we are" we can say:

SELECT COUNT(*) FROM dbo.MessageTable WITH (NOLOCK)
  WHERE CampaignID = x AND CustomerID = y;

A lot of people will suggest using this for COUNT(*) with no WHERE clause. I would argue that if you're willing to accept some inaccuracy in COUNT(*) you may as well do:

SELECT SUM(rows) FROM sys.partitions
  WHERE [object_id] = OBJECT_ID('dbo.tablename')
  AND index_id IN (0,1);

This number is similarly inaccurate due to in-flight transactions, but doesn't actually have to scan the table, so it is far more efficient. For our case we could use this even for subsets: with a filtered index in place (for other purposes) we could similarly query sys.partitions but use the index_id of the filtered index.

In most cases, though, using NOLOCK may feel like a turbo button, but the inaccuracy it may cause is very rarely worth it. Unless your system is already heavily tempdb-bound, you should consider READ_COMMITTED_SNAPSHOT for your current NOLOCK scenarios instead. See Pros and cons of READ_COMMITTED_SNAPSHOT

Answer 2

I usually use this to query a relatively busy table I normally use for logging.

SELECT TOP 10 * FROM dbo.MessageLog (NOLOCK) WHERE AppCode = 'DesktopApp' ORDER BY MessageDate DESC

The table's records are mainly written once, and never updated.