Read committed Isolation in SQL Server

This is the default isolation level and means selects will only return committed data. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in no repeatable reads or phantom data.

Example:

---Query1

BEGIN TRAN 

UPDATE Employee SET CreatedDate = getdate()

--Simulate having some intensive processing here with a wait

WAITFOR DELAY '00:00:05' 

ROLLBACK   

---Query 2  

---Default Isolation level is Read committed

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT * FROM Employee

 Result:

query 2 display only committed data ,only committed data allowed to read

 If you want to check what isolation level you are running under you can run “DBCC user options”. Remember isolation levels are Connection/Transaction specific so different queries on the same database are often run under different isolation levels.