A Difference Between Nolock and With Nolock In SQL Server

Table of Content

• Introduction
• What is Nolock in SQL Server?
• What is With (Nolock) in SQL Server?
• Conclusion

Introduction

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 in SQL Server Database.

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.

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 on with nolock in SQL as well as no lock in SQL server DB .