Select *
from MinhaTabela with (NOLOCK)
I know colleagues who always insert the With (NOLOCK) clause in their selects and I know others who preach that NOLOCK is bad practice and should never be used.
Some claim that with NOLOCK we have more performance, others say they can generate errors by typing "dirty" records.
Extremists frighten me. What is the point of balance? That is, when to use WITH (NOLOCK) in my queries?
Your question is very interesting and it is much deeper than my simple answer and depends on factors such as: What you want with the query, the DBMS in question and type of reading you intend.
Let's define two concepts before:
Unclear or dirty reading "dirty read": read data from a table that was not committed by commit. That is, data that is temporarily there and may disappear.
committed read read: read data that has been committed and will not disappear in the middle of a transaction at that time, for example.
When we use NOLOCK and the DBMS allows this, dirty reading data will be returned mixed with confirmed data. Already, if you avoid use, only the data that has been committed commit will be displayed.
Therefore, a data that is not "committed" should be treated as a data that does not exist for certain situations. There are rare cases where you need a temporary "unrecorded" reading on the table in question, as they might "go away" given some situation and you get inconsistent results in return.
In summary: If your DBMS allows, use NOLOCK when you want to see unconfirmed data and not when you want to work only with data that is actually confirmed.
I hope I was didactic, explaining the difference. A hug