use scratch
drop table t
drop table #t
drop table #u
create table t (id int, val varchar(20))
insert t values(2,'Original')
go
alter database scratch set allow_snapshot_isolation on
alter database scratch set read_committed_snapshot off
go
set transaction isolation level read committed
set nocount on

begin tran
select Val, getdate() from t where id=2

declare @i int
set @i=0
while @i<5
begin
select a.* into #t from sysobjects a, sysobjects b
select top 1 * into #u from #t
drop table #t
drop table #u
set @i = @i+1
end

select Val, getdate() from t where id=2

rollback tran

use scratch

set transaction isolation level read committed
set nocount on

begin tran

select val, getdate() from t where id=2
update t set val='gotcha' where id = 2
select val, getdate() from t where id=2

declare @i int
set @i=0
while @i<5
begin
select a.* into #t from sysobjects a, sysobjects b
select top 1 * into #u from #t
drop table #t
drop table #u
set @i = @i+1
end

rollback tran

Script 1:
alter database scratch set allow_snapshot_isolation on
alter database scratch set read_committed_snapshot on
go
set transaction isolation level snapshot

Val
-------------------- -----------------------
Original 2009-03-05 14:45:28.533
Original 2009-03-05 14:45:45.800

Script 2:
set transaction isolation level snapshot

val
-------------------- -----------------------
Original 2009-03-05 14:45:30.770
gotcha 2009-03-05 14:45:30.770

Script 1:
alter database scratch set allow_snapshot_isolation on
alter database scratch set read_committed_snapshot on
go
set transaction isolation level read committed

Val
-------------------- -----------------------
Original 2009-03-05 14:49:38.690
Original 2009-03-05 14:49:55.517

Script 2:
set transaction isolation level read committed

val
-------------------- -----------------------
Original 2009-03-05 14:49:40.893
gotcha 2009-03-05 14:49:40.893

Script 1:
alter database scratch set allow_snapshot_isolation on
alter database scratch set read_committed_snapshot off
go
set transaction isolation level read committed

Val
-------------------- -----------------------
Original 2009-03-05 14:53:53.733
Original 2009-03-05 14:54:08.673

Script 2:
set transaction isolation level read committed

val
-------------------- -----------------------
Original 2009-03-05 14:53:58.280
gotcha 2009-03-05 14:53:58.280

Script 1:
alter database scratch set allow_snapshot_isolation on
alter database scratch set read_committed_snapshot off
go
set transaction isolation level snapshot

Val
-------------------- -----------------------
Original 2009-03-05 15:12:49.790
Original 2009-03-05 15:13:09.010

Script 2:
set transaction isolation level snapshot

val
-------------------- -----------------------
Original 2009-03-05 15:12:58.633
gotcha 2009-03-05 15:12:58.633

William Vach (3/5/2009)

---

Shouldn't the where clause of the 3rd statement be "where id = 2"?

When snapshot isolation is set, it guarantees transaction-level read consistency where every statement within a snapshot isolation transaction sees only committed changes that occurred before the start of the transaction. Effectively, each statement in the transaction sees the same set of data while the data is available for modification outside of this transaction. Concurrent modifications are not prevented and this "snapshot" transaction is unaware of the changes that are made by other transactions. The version "refresh" occurs only in the start of each transaction as long as you run under snapshot isolation

Under the heading:
Read Committed Isolation Using Row Versioning (Statement-Level Read Consistency)

[quote]When set, statement-level read consistency guarantees that each statement under read committed isolation sees only committed changes that occurred before the start of the statement. Each new statement within the transaction picks up the most recent committed changes. In other words, this version of read committed is semantically similar to traditional read committed in that only committed changes are visible, but the timing of when those changes committed differs. Each statement sees the changes that were committed before the statement began instead of when the resource is read.

In SQL Server 2005, to use row versioning–based isolation, one of the following database options must already be set (and not pending):

Read committed isolation using row versioning for statement-level read consistency
Snapshot isolation for transaction-level read consistency