How to Read Uncommitted Rows from SQL Server Table by using WITH (NOLOCK) Hint - TSQL Tutorial

By default isolation level of SQL Server Database Engine is READ COMMITTED. That mean if we try to select the rows, it will only return us only committed rows.

To Check the Default Isolation level of SQL Sever we can use

DBCC useroptions 

Fig 1: DBCC useroptions output to check Isolation level of SQL Server

Let's consider couple of scenarios where we want to read uncommited data

1. We are deleting alot of records from Table and want to take a look on progress
2. We are inserting a lot of records by using SSIS Package, even the records are not committed, we want to see the progress of load.

OR

Sometime we are preparing our Scripts for deployments and we run the statement and then want to see if script worked as expected before committing the transaction.

WITH (NOLOCK) is hint that we can use to read dirty or uncommitted records.

Let's create a table in one of our Database by using below DDL Script and Insert couple of Records

USE TestDB
go
Create Table dbo.TblCustomer(
Id int,
NAME VARCHAR(100),
Region VARCHAR(100)
)
go
insert into dbo.TblCustomer values (1,'Aamir',NULL)
go
Insert into dbo.TblCustomer values (2,'Raza',Null)

Now let's Begin our Transaction in a connection and update the Region Column with value='NA' as shown below.

BEGIN TRAN
Update dbo.TblCustomer
set Region='NA'

Fig 2: Update the Records by using Begin Transaction in SQL Server

As we can see in Fig 2. Two rows are effected.  As we did not Commit the Transaction. The records are still not committed to the table. Let's open a new query windows (Connection/Session) and try to Select the Rows from dbo.tblCustomer table.

Fig 3: Trying to read data from Table on which we have uncommitted transaction

As we can see that in Fig 3, It is trying to read the database but not returning anything. The reason it is not returning any results because we have Read Commit Isolation level on SQL Server Database Engine and records are not committed those we start updating in Fig 2. The query can only return committed records. 

To read the uncommitted records we can use WITH (NOLOCK) hint and see the dirty or uncommitted rows.

Fig 4: Read uncommitted records by using WITH (NOLOCK) Hint in SQL Server

As we can see that we even did not commit the Transaction that we opened in Fig 2 but by using WITH ( NOLOCK) Hint on Table, we are table to read the dirty or uncommitted records. Now at this point if we feel that our script worked as expected, we can go ahead and commit Transaction by using COMMIT TRAN or we can rollback by using ROLLBACK TRAN.

SQL Server Video Tutorial: How to Read uncommitted Rows/ Dirty Records