Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server - how to set (nolock) hint as a default?

is there some way to tell sql server to use the (nolock) hint or every select in a stored procedure?

is pretty tiresome to add it to each an every select....

like image 268
opensas Avatar asked Jun 23 '09 23:06

opensas


People also ask

What is Nolock hint in SQL Server?

The NOLOCK hint allows SQL to read data from tables by ignoring any locks and therefore not get blocked by other processes. This can improve query performance by removing the blocks, but introduces the possibility of dirty reads.

Is with Nolock deprecated?

TT. WITH (NOLOCK) is not deprecated for SELECT queries.

What is difference between Nolock and with Nolock in SQL Server?

Thus, we can say that Nolock reads “Dirty Data” when applied with only Select statement in SQL Server Database. While With (Nolock)do not issue any shared locks and exclusive locks. It is possible with With (Nolock) that, it can read an uncommitted transaction, which can be rolled back at the middle of a read.


3 Answers

As others quite rightly say, a global (nolock) is done using READ UNCOMMITTED.

However, before going down that route, it's worth trying READ COMMITTED SNAPSHOT first. This means your reads won't be locked by in progress inserts / updates and means that the data isn't dirty, just out of date.

like image 111
Robin Day Avatar answered Oct 16 '22 05:10

Robin Day


You want to use the following syntax:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

I found this by looking at the NOLOCK table hint located here. The WITH(NOLOCK) table hint is equivalent to setting the isolation level to be READ UNCOMMITTED. Here's the snippet from MSDN:

NOLOCK Is equivalent to READUNCOMMITTED. For more information, see READUNCOMMITTED later in this topic.

like image 30
Dan Wolchonok Avatar answered Oct 16 '22 04:10

Dan Wolchonok


I think this is what you are looking for...

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

like image 41
bytebender Avatar answered Oct 16 '22 03:10

bytebender