Q:

Read committed Snapshot VS Snapshot Isolation Level

Could some one please help me understand when to use SNAPSHOT isolation level over READ COMMITTED SNAPSHOT in SQL Server? 
I understand that in most cases READ COMMITTED SNAPSHOT works, but not sure when go for SNAPSHOT isolation. 
Thanks

A:

READ COMMITTED SNAPSHOT does optimistic reads and pessimistic writes.   In contrast, SNAPSHOT does optimistic reads and optimistic writes. 
Microsoft recommends READ COMMITTED SNAPSHOT for most apps that need row versioning. 
Read this excellent Microsoft article: Choosing Row Versioning-based Isolation Levels.    It explains the benefits and costs of both isolation levels.    
And here's a more thorough one:
http://msdn.microsoft.com/en-us/library/ms345124(SQL.90).aspx

A:

[![ Isolation levels table][2]][2]
See the example below:
Read Committed Snapshot
Change the database property as below
ALTER DATABASE SQLAuthority
SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE
GO

Session 1
USE SQLAuthority
GO
BEGIN TRAN
UPDATE DemoTable
SET i = 4
WHERE i = 1

Session 2
USE SQLAuthority
GO
BEGIN TRAN
SELECT *
FROM   DemoTable
WHERE i = 1

Result – Query in Session 2 shows old value (1, ONE) because current transaction is NOT committed.  This is the way to avoid blocking and read committed data also. 
Session 1
COMMIT

Session 2
USE SQLAuthority
GO
SELECT *
FROM   DemoTable
WHERE i = 1

Result – Query in Session 2 shows no rows because row is updated in session 1.  So again, we are seeing committed data. 
Snapshot Isolation Level
This is the new isolation level, which was available from SQL Server 2005 onwards.  For this feature, there is a change needed in the application as it has to use a new isolation level. 
Change database setting using below.  We need to make sure that there is no transaction in the database. 
ALTER DATABASE SQLAuthority SET AllOW_SNAPSHOT_ISOLATION ON

Now, we also need to change the isolation level of connection by using below
Session 1
USE SQLAuthority
GO
BEGIN TRAN
UPDATE DemoTable
SET i = 10
WHERE i = 2

Session 2
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
GO
USE SQLAuthority
GO
BEGIN TRAN
SELECT *
FROM   DemoTable
WHERE i = 2

Result- Even if we have changed the value to 10, we will still see old record in session 2 (2, TWO). 
Now, let’s commit transaction in session 1
Session 1
COMMIT

Let’s come back to session 2 and run select again. 
Session 2
SELECT *
FROM   DemoTable
WHERE i = 2

We will still see the record because session 2 has stated the transaction with snapshot isolation.  Unless we complete the transaction, we will not see latest record. 
Session 2
COMMIT
SELECT *
FROM   DemoTable
WHERE i = 2

Now, we should not see the row as it's already updated. 
See: SQL Authority, Safari Books Online

A:

No comparison of Snapshot and Snapshot Read Committed is complete without a discussion of the dreaded "snapshot update conflict" exception that can happen in Snapshot, but not Snapshot Read Committed.  
In a nutshell, Snapshot isolation retrieves a snapshot of committed data at the start of a transaction, and then uses optimistic locking for both reads and writes.   If, when attempting to commit a transaction, it turns out that something else changed some of that same data, the database will rollback the entire transaction and raise an error causing a snapshot update conflict exception in the calling code.   This is because the version of data affected by the transaction is not the same at the end of the transaction as it was at the start. 
Snapshot Read Committed does not suffer from this problem because it uses locking on writes (pessimistic writes) and it obtains snapshot version information of all committed data at the stat of each statement.   
The possibility of snapshot update conflicts happening in Snapshot and NOT Snapshot Read Committed is an extremely significant difference between the two.