Difference between NOLOCK and WITH NOLOCK in SQL SERVER

 From time to time, SQL Server information base executives wind up in conflicts with their application designer partners – especially with regards to a portion of the last's Transact SQL (T-SQL) formative practices. One of my first perceptions when I joined my present boss is that practically all T-SQL scripts composed by application designers utilizes the NOLOCK table clue. Notwithstanding, from the associations that I have had with these regarded designers it doesn't seem like they see how the NOLOCK table clue functions. Besides, despite the fact that they appear to know about a differentiation among NOLOCK and the WITH NOLOCK table clue, they again don't appear to understand how the two vary from each other. In this article, I investigate the interior functions of the NOLOCK table indicate and inspect the ramifications of discarding the WITH watchword.

The data set motor applies some remarkable kinds of controls, when it adjusts its information. These remarkable controls are known as locks. These locks show, that the information base records were locked in with the exchanges and theselocks keeps up the data set trustworthiness. 

Typically,the SQL Server utilizes various kinds of locks to separate the exchange in various levels. In this article, we will examine the distinction among Nolock and With Nolock in SQL Server Database.

What is Nolock in SQL Server?

The Nolock can also be called as READUNCOMMITTED and it only applied inSELECT statements. It specifies that no shared locks can be issued against the table, which preventsother transactions from modifying the data in table.

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 testtable we can see that the table still has locks issued against it. Now, in the other query window, execute the below query with Nolock table hint to check for record inserted in it.

SELECT COUNT(*) FROM testtable WITH(NOLOCK)

The select statement returns 11records, so the transaction that I inserted in testable is still not committed. This means I can still rollback the inserted transactions by executing the command given below in query window.

Rollback Transaction

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

SELECT COUNT(*) FROM testtable WITH(NOLOCK)

The above statement 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?

It is an explicit command, which directly specify for particular table or a view. It 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 the memory space.

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