What is Difference Between (nolock) and with(nolock) in SQL Server

The database engine utilizes several exceptional types of controls once it modifies its data. Those exceptional controls are called locks. Those locks signify that the database records were engaged with the transactions. Those locks maintain the database integrity.

Usually, the SQL Server utilizes different types of locks to separate the transaction in different levels. In this page, we are going to discuss the difference between Nolock and With Nolock in SQL Server.

WHAT IS NOLOCK IN SQL SERVER?

You have to know that Nolock can also be called as READUNCOMMITTED. It only applied in ‘SELECT’ statements. It determines that no shared locks are able to be issued against the table, which prevents other transactions from modifying the data in the table.

• More info: fontdrvhost.exe Usermode Font Driver Host – Why is It Running?

We are able to take an instance to see how Nolock works in SQL Server:

BEGIN TRANSACTION

INSERT INTO test table

(Product, SaleDate, SalePrice)

VALUES

(‘PoolTable’, GETDATE, 500)

After inserting a record into testable, you are able to see that the table still has locks issued against it. Now, in the other query window, you will be able to execute the query with a Nolock table hint to check for records inserted in it. The SELECT statement returns 11 records. Therefore, the transaction that you insert in testable is still not committed. It means that you are still able to rollback the inserted transactions by executing the command in the query window.

Rollback Transaction

The Rollback statement deletes the record from testable. To check the changes, you are able to execute the select statement. For your information, the statement returns ten records.

WHAT IS WITH (NOLOCK) IN SQL SERVER?

Basically, it is an explicit command that directly specifies a particular table or a view. Actually, it is similar to the Nolock hint. It does not utilize locks against table’s data when the command is issued. There are benefits of using With Nolock.

The benefit is no deadlock is encountered against the table’s queries running against the table. Also, there is no requirement to hold the locks against the data, that will save the memory space. While using WITH (nolock), there is no need to do anything with sub queries.

But, you are able to use it with single or sub queries. The Nolock and WITH (nolock) are similar as transaction isolation level. However, using WITH (nolock) can be unsafe, as it will be able to return inconsistent results.

DIFFERENCE BETWEEN SQL SERVER NOLOCK AND WITH NOLOCK

As you are able to see the difference between Nolock and With (Nolock) is that, the Nolock reads that data, which has not committed and is able to be rolled back. Thus, we are able to say that Nolock reads “Dirty Data” once applied with only ‘Select’ statement in SQL Server Database.

While With (Nolock) does not issue any shared locks and exclusive locks. It is possible with With (Nolock) that it is able to read an uncommitted transaction, that can be rolled back at the middle of a read.

ADVANTAGES OF USING THE WITH KEYWORD

You will still retrieve similar results as shown in the Script 3 and Figure by adding the WITH keyword in front of the NOLOCK hint. If the output of scripts using NOLOCK and WITH NOLOCK table hints is identical, why do we worry about using the one over the other? Well, it turns out that there are actually some differences between the two:

1. Support for hints without the WITH keyword will be deprecated soon.

Microsoft documentation displayed in Figure 5 by continuing to exclude the WITH keyword in a table hint. It means that you are increasing your technical debt. You need to go back and remake your scripts after this functionality is deleted in future versions of SQL Server.

2. Determine Multiple Table Hints using the WITH keyword

Aside from the fact that the creators of SQL Server have instructed us to the WITH keyword once specifying table hints, another advantage of using the WITH keyword is that you are able to include multiple table hints against the same table as shown in the Script 4. If you determine multiple hints after having relieved the WITH keyword is going to result easily into the error shown in the Script 5 and Figure 6 respectively.

3. Without the WITH Keyword you easily have a table Alias

At this point, you should already be determining towards always specifying the WITH keyword once you are when using the table hints but only to ensure you further, losing the WITH keyword is able to have undesirable consequences.

If you forget to include NOLOCK inside round brackets, so the NOLOCK hint is able to be mistaken for a table by the query optimizer. It means that the query will have to wait for shared lock before it is able to start reading a given table.

For example, you forgot to include NOLOCK inside opening and closing round brackets. This is going to lead to the query executing endlessly because it waited for a shared lock.

There are advantages and drawbacks to determining NOLOCK table hints as a result they should not only be included in every T-SQL script without a clear understanding of what they do. Nevertheless, if a decision be created to utilize NOLOCK table hint, we suggest you to include the WITH keyword.

• Don’t miss: How to Convert CRT to PEM Easily

Conclusion

Well, this is an explanation about the difference between Nolock and With (Nolock) in SQL Server. Now, you are able to know what is its difference. As we explained above, the difference between Nolock and With (Nolock) is that, the Nolock reads that data, which has not committed and is able to be rolled back.

By the way, we are able to say that Nolock reads “Dirty Data” once applied with only ‘Select’ statement in SQL Server Database. While With (Nolock) does not issue any shared locks and exclusive locks. It is possible with With (Nolock) that it is able to read an uncommitted transaction, that can be rolled back at the middle of a read.