Is NOLOCK always bad?

It isn't always bad.

Of course it allows you to read uncommitted values (that may be rolled back and hence never logically existed) as well as allowing phenomena such as reading values multiple times or not at all.

The only isolation levels that guarantee that you won't encounter any such anomalies are serializable/snapshot. Under repeatable read values can be missed if a row is moved (due to a key update) before the scan reaches this row, under read committed values can be read twice if a key update causes a previously read row to move forward.

These issues are more likely to arise under nolock however because, by default, at this isolation level it will use an allocation ordered scan when it estimates there is more than 64 pages to be read. As well as the category of issues that arise when rows move between pages due to index key updates these allocation ordered scans are also vulnerable to issues with page splits (where rows can be missed if the newly allocated page is earlier in the file than the point already scanned or read twice if an already scanned page is split to a later page in the file).

At least for simple (single table) queries it is possible to discourage the use of these scans and get a key ordered scan at nolock by simply adding an ORDER BY index_key to the query so that the Ordered property of the IndexScan is true.

But if your reporting application doesn't need absolutely precise figures and can tolerate the greater probability of such inconsistencies it might be acceptable.

But certainly you should not be chucking it on all queries in the hope that is a magic "turbo" button. As well as the greater probability of encountering anomalous results at that isolation level or no results at all ("Could not continue scan with NOLOCK due to data movement" error) there are even cases where the performance with nolock can be much worse.

If your report blocks updates that your DBA is right: you should absolutely not use NOLOCK. The very fact that there are conflicts is a clear indication that if you would use dirty reads you would get incorrect reports.

In my opinion, there are always better alternatives than NOLOCK:

• Are your production tables read only in effect and never get modified? Mark the database read only!
• Table scans cause lock conflicts? Index the tables appropriately, the benefits are multiple.
• Can't modify/don't know how to index appropriately? Use SNAPSHOT ISOLATION.
• Can't change app to use snapshot? Turn on read committed snapshot!
• You have measured the impact of row versioning and have evidence it impacts performance? You can't index the data? and you are OK with incorrect reports? Then at the very least do yourself a favor and use SET TRANSACTION ISOLATION LEVEL, not a query hint. It will be easier to later fix the isolation level instead of modifying every query.

Do your customers tolerate inconsistent results in reports? If the answer is no, you should not use NOLOCK - you can get wrong results under concurrency. I wrote a few examples here, here, and here. These examples show inconsistent output under READ COMMITTED and REPEATABLE READ, but you can tweak them and get wrong results with NOLOCK as well.