When should you use the NOLOCK hint?
Use NOLOCK when you need to run a heavy query on a table, retrieve data from a table that constantly updates, or when you don't know how many records your query will retrieve and how it will affect the database.
When you use the NOLOCK hint, your query is not blocked by other processes because it ignores any locks when reading data from tables.
Note that using the NOLOCK hint can improve query performance, but it introduces the possibility of dirty reads.
Example
SELECT * FROM Person.Contact WITH (NOLOCK) WHERE ContactID < 20
In the above example, we want to avoid locks in the Person.Contact table.
For more details, see these articles:
- https://www.mssqltips.com/sqlservertip/2470/understanding-the-sql-server-nolock-hint/
- https://www.sqlshack.com/understanding-impact-clr-strict-security-configuration-setting-sql-server-2017/
