Drawbacks of using NOLOCK

• Using NOLOCK might seem a good idea at first as we get the requested data faster without waiting for the other operation to be committed. However, dirty read is a huge problem, the result that we get after that, may not be accurate. So automatically adding NOLOCK hints is generally not a good practice in an Online Transaction Processing (OLTP) environment.
• Adding the NOLOCK hint or changing the isolation level does change how either locking is handled for either an individual query or for all commands in the session. Test these changes thoroughly to check if they meet your needs.
• If you are performing aggregate functions like SUM(), AVG(), MAX(), MIN(), etc without lock then the result is inconsistent due to ongoing updates by other transactions.

Using the SELECT statement with an ongoing INSERT or UPDATE statement, put an exclusive lock on rows or possibly on the whole table until the operation’s transaction is committed or rolled back. Suppose, you are working on a very big table with thousands of rows and the database table is not efficiently designed. Remember, in real life, you are not the only one working with a database, that database might be getting accessed on many users’ devices at the same time. So, if you are reading the table using the SELECT statement and someone else is trying to execute the INSERT statement, then a lock can occur and the two transactions block each other.

So the solution in SQL Server is that the NOLOCK table hint allows you to instruct the query optimizer to read a given table without obtaining an exclusive or shared lock.