A Difference Between Nolock and With Nolock In SQL Server

The database engine applies some exceptional types of controls when it modifies its data. These exceptional controls are known as locks. These locks indicate, that the database records were engaged with the transactions and these locks maintain the database’s integrity. If you want to know if there is a difference between nolock and with nolock in SQL server DB, stay tuned till the end of this article.

Typically, the SQL Server uses different types of locks to isolate the transaction at different levels. In this article, we will discuss the difference between Nolock and With Nolock.

Table of Content

• User Queries
• What is Nolock in SQL Server?
• What is With (Nolock) in SQL Server?
• Advantages of Using the WITH Keyword
  • 1. Hints Support
  • 2. Specify Multiple Table Hints
  • 3. Table Alias
• Conclusion

NOLOCK Vs With NOLOCK User Queries

Well, before we proceed in this article to find out all the explanations we are looking for, let’s have a look at the user queries. Below we’re mentioning the most asked user queries to get a  clear perspective of the entire scenario.

User Query 1 

User Query 2

As we can see, most of the users need clarification about the difference between both of them. Let’s proceed further to know this topic in detail.

We can take an example to see how Nolock works:

BEGIN TRANSACTION
      INSERT INTO testtable
      (Product, SaleDate, SalePrice)          
      VALUES            
      ('PoolTable', GETDATE(), 500)      

After inserting a record into the test table we can see that the table still has locks issued against it. Now, in the other query window, execute the below query with the no lock in SQL table hint to check for the record inserted in it.

SELECT COUNT(*) FROM testtable WITH(NOLOCK)

The NOLOCK in SQL Server select statement returns 11 records, so the transaction that I inserted in the test table is still not committed. This means I can still roll back the inserted transactions by executing the command given below in the query window. This is a key element in understanding nolock vs with nolock in depth.

Rollback Transaction

The Rollback Transaction statement removes the record from the test table. To check the changes we can execute the select statement:

SELECT COUNT(*) FROM Test Table WITH(NOLOCK)

The above statement for NOLOCK in SQL returns 10 records, so we can understand that the Nolock intimates to keep the database engine from issuing locks against the tables.

What is With (Nolock) in SQL Server?

When we talk about SQL with nolock, we can say that it is an explicit command. This directly specifies a particular table or a view & is similar to Nolock hint. It does not use locks against table’s data, once the command is issued. The benefit of using With Nolock is that no deadlock is encountered against the table’s queries running against the table; also, there is no need to hold the locks against the data, which will save memory space.

While using WITH (nolock) there is no need to do anything with subqueries. However, you can use with nolock in SQL along with single or subqueries. The Readuncommited and WITH (nolock) are similar to the transaction isolation level. But, using WITH (nolock) could be unsafe, because it will return inconsistent results.

Using WITH NOLOCK – Know the Benefits

If we try to get the jersey number of Indian cricketer MS Dhoni using the WITH NOLOCK attribute, users will get similar results that they will get without using that attribute. Have a look at the query:

SELECT [Jersey Number]      
  FROM [SQLSYS].[dbo].[2016_Cricket] WITH (NOLOCK)
  WHERE [Nominee] = 'MS Dhoni'

Now, most of the user says that there is no difference between these two commands. Well, If we look at the results, we get the same. Then what is the need to prefer one over another? Understand the below benefits to find out difference between nolock and with nolock attributes.

1. Hints Support without WITH Keyword will be Deprecated

According to Microsoft, if users continue to exclude the WITH keyword in the hint, shows the intent that a user is gaining more technical debt. Also, they have to refactor the scripts after the functionality is removed in SQL Server future versions. Users can see that in the below figure:

2. Use WITH Keyword to Specify Multiple Table Hints

The SQL Server suggests users utilize the WITH keyword in case they specify table hints. However, there is another benefit as well. Another benefit is that a user can easily include multiple table hints for the same table. Example:

SELECT 
[Jersey Number]      
  FROM [SQLSYS].[dbo].[2016_Cricket] WITH (TABLOCK, HOLDLOCK)
  WHERE [Nominee] = 'Virat Kohli'

Now, after having omitted the WITH keyword, if a user specifies multiple hints, the SQL Server results with the Msg 207,  Level 16, State 1, line 16 error.

3. Table Alias without WITH Keyword to Understand NOLOCK vs WITH NOLOCK

Now, another benefit of using the WITH Keyword is that users no more need to use the round brackets. If users accidentally use NOLOCK without specifying the WITH keyword, the table might understand that as a table Alias. As a result, users’ queries have to wait for the shared lock before initiating the reading of the table.

Conclusion

As we can see the difference between Nolock and With (Nolock) is that, the Nolock reads that data, which has not been committed and can be rolled back. Thus, we can say that Nolock reads “Dirty Data” when applied with only the Select statement in SQL Server Database.

While With nolock in SQL does not issue any shared locks or exclusive locks. It is possible with With (Nolock) that, it can read an uncommitted transaction, which can be rolled back in the middle of a read. I hope that now users are clear about difference between nolock and with nolock  attributes.