SNAPSHOT Isolation in SQL Server
Except
when a database is being recovered, SNAPSHOT transactions do not request locks
when reading data. SNAPSHOT transactions reading data do not block other
transactions from writing data. Transactions writing data do not block SNAPSHOT
transactions from reading data.
During the roll-back phase of a database recovery,
SNAPSHOT transactions will request a lock if an attempt is made to read data
that is locked by another transaction that is being rolled back. The SNAPSHOT
transaction is blocked until that transaction has been rolled back. The lock is
released immediately after it has been granted.
In SNAPSHOT row versioning is used so when
data is changed the old version is kept in tempdb so existing transactions will
see the version without the change. When all transactions that started before
the changes are complete the previous row version is removed from tempdb. This
means that even if another transaction has made changes you will always get the
same results as you did the first time in that transaction.
So
the plus side you’re not blocking anyone else from modifying the data whilst
you run your transaction but…. You’re using extra resources on the SQL Server
to hold multiple versions of your changes.
To
use the snapshot isolation level you need to enable it on the database by
running the following command
Syntax:
ALTER DATABASE DatabseName
SET ALLOW_SNAPSHOT_ISOLATION ON
Example:
ALTER DATABASE LocalTest
SET ALLOW_SNAPSHOT_ISOLATION ON
--Query1
BEGIN TRAN
SELECT * FROM Employee
WAITFOR DELAY '00:00:10'
SELECT * FROM Employee
ROLLBACK
--Query2
INSERT INTO Employee(EmployeeName,Country,PrimaryLanguage,CompanyPlant,ManagerId)
VALUES
('Anushka', 'England','English','London',5)
Result:

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.