Read Dirty Records in SQL Server

Sometimes we need to read Dirty / Uncommitted records to confirm the changes made by other users.

To read uncommitted records we have SET the Isolation level to read uncommitted. check below example.

Formate to write the SQL

set transaction isolation level read uncommitted select * from dbo.code where  id ='123'

look at the "isolation level read uncommitted" part, which makes you SQL to read dirty records/data.

Now go for a full example:

Let there are two users,

USER-1 will make the changes and rollback at the end

USER-2 will read the uncommitted data from other sessions

Before start making changes, data looks like 

ID	Name
123	Saiful

USER-1: started the transaction...

begin transaction      update dbo.code    set name = 'azam'   where id='123'

USER-2: Read this Dirty/ Uncommitted/ Unrollbacked record

set transaction isolation level read uncommitted select * from dbo.code where  id ='123'

S/he will get below data BUT the transaction has not been committed OR Rollbacked

USER-1: now rollback this transaction 

rollback

USER-2: if s/he runs the SQL then s/he will get the actual data!

select * from dbo.code where  id ='123'

will get below data

ID	Name
123	Saiful