SQL-NO LOCK

Q Why and when should one use NO LOCK in SQL query ?

 "WITH (NO LOCK) " is used in SQL Server query to speed up the execution of query. This is particularly used when we are running a long query which fetches excessively a large number of records from a table and we are not much bothered about data integrity.  What exactly happens is that when we fire a SQL query ,database puts a lock on a table. So one process who is in need of  the same table will have to wait till the other process completes reading or writing of a table thus making the query slower. In such scenario where data integrity is not a problem , we can use NO LOCK to avoid lock on a table. Thus this process will read the data without applying lock on a table and also helping other processes as they do not have to wait.
       Use "WITH(NO LOCK) in the from clause just after the table name as shown below.

 SELECT Name, ProductName,Quantity, MRP
 FROM CustomerInfo C WITH(NOLOCK)
 LEFT OUTER JOIN ProductInfo P WITH(NOLOCK)
ON C.ProductID=P.ProductID

WITH(NOLOCK) Illustration