What is (are) difference between NOLOCK and UNCOMMITTED

NOLOCK : Is equivalent to READ UNCOMMITTED (source : MSDN)

NOLOCK or READ UNCOMMITTED Specifies that dirty reads are allowed. No shared locks are issued to prevent other transactions from modifying data read by the current transaction, and exclusive locks set by other transactions do not block the current transaction from reading the locked data. Allowing dirty reads can cause higher concurrency, but at the cost of reading data modifications that then are rolled back by other transactions

READ UNCOMMITTED and NOLOCK hints apply only to data locks. All queries, including those with READ UNCOMMITTED and NOLOCK hints, acquire Sch-S (schema stability) locks during compilation and execution. Because of this, queries are blocked when a concurrent transaction holds a Sch-M (schema modification) lock on the table

Under the hood they are the performing the same action.

The READ UNCOMMITTED isolation level is the least restrictive isolation level within SQL Server, which is also what makes it popular for developers when looking to reduce blocking.

The NOLOCK table hint behind the scenes performs the exact same action as running under the read-uncommitted isolation level.

The only difference between the two is that the READ UNCOMMITTED isolation level determines the locking mechanism for the entire connection and the NOLOCK table hint determines the locking mechanism for the table that you give the hint to.

No difference in terms of their functions, like other have mentioned.

The single difference is that you can apply WITH(NOLOCK) selectively, on some tables but not others. READ UNCOMMITTED applies NOLOCK to all tables in a session.

If you do this:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSELECT *FROM Table1 T1INNER JOIN Table2 T2 ON T1.ID = T2.id

It is functionally equivalent to:

SELECT *FROM Table1 T1 WITH(NOLOCK)INNER JOIN Table2 T2 WITH(NOLOCK) ON T1.ID = T2.ID

But you can also apply WITH(NOLOCK) selectively:

SELECT *FROM Table1 T1 WITH(TABLOCK)INNER JOIN Table2 WITH(NOLOCK) ON T1.ID = T2.ID