In SQL Server, as you execute a query, the database employs locks so that nothing else is modifying the data that you are operating on. This stops problems such as dirty reads (reading uncommitted data) or phantom reads (reading rows that have been inserted or deleted during your transaction).

But occasionally, you may not want to lock the data when you're executing SELECT statements—particularly if you're dealing with a big database or want to make sure you have good performance. That's where the WITH (NOLOCK) hint is useful.

Select * from CountryMaster with (nolock)

*How Does WITH (NOLOCK) Work?

Typically, when SQL Server executes a query, it will lock the pages or rows of data to keep other transactions from modifying the data while you read it. This guarantees data consistency and accuracy.

But if you use WITH (NOLOCK), SQL Server bypasses these locks. What this implies is that your query does not have to wait for other transactions, potentially resulting in improved performance. But it adds the possibility of reading data that's inconsistent or perhaps no longer exists (this is a dirty read).

*When Should You NOT Use WITH (NOLOCK)?

Though WITH (NOLOCK) can be helpful at times, there are also situations when you would not want to use it:

When consistency matters: If you're dealing with financial transactions, inventory information, or any information that needs to be consistent, do not use WITH (NOLOCK).

When updating or altering data: Applying WITH (NOLOCK) with UPDATE, INSERT, or DELETE statements may produce inaccurate results. Modifying data using NOLOCK is not supported in SQL Server, yet applying NOLOCK for selection while performing other actions might be a problem.

When operating within high-transaction scenarios: In case your database is subjected to heavy concurrent reads and writes, applying NOLOCK may result in serious inconsistencies.