Server-side NOLOCK in SQL Server

Question

I know that in the Oracle DB I can configure a flag, that all select queries running on a specific DB can be run as if the NOLOCK hint was added. Is there something similar in SQL Server?

Answer 1

Not at a per-database level. You can only do it with hints or set transaction isolation level. You can set a database to read-only mode, which may have that effect by default but which will (as the name suggests) preclude writes to the database.

This might be useful for a database that is periodically refreshed. You set the database to read-only and set it to writeable for the updates then back to read-only.

Answer 2

You can set the database into READ_ONLY mode, which certainly changes some of the transaction rules, but I don't think there's anything closer to a global NOLOCK.

Edit

If you want to be able to perform DELETEs, INSERTs and UPDATEs, but you don't want readers to lock or have to specify hints or isolation levels, you could also consider turning on READ_COMMITTED_SNAPSHOT, but the semantics there are again not quite the same as the NOLOCK hint.

Answer 3

Not for the whole database but for each query you do:

SELECT a, b
FROM t1
WITH (NOLOCK)
WHERE t1.c = @value

or

SELECT a, b
JOIN t1
ON t1.id = t2.id
WITH (NOLOCK)
FROM t2
WHERE t2.c = @value

etc