Read committed Snapshot VS Snapshot Isolation Level

sql-server snapshot isolation-level read-committed-snapshot
user173552 picture user173552 · Apr 29, 2010 · Viewed 58k times · Source

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

Answer

Bill Paetzke picture Bill Paetzke · May 6, 2010

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

Related questions

Alternatives to snapshot functionality, SQL Server Standard

For testing purposes I would like to reset a complete database to a certain state (data and structure). I would like to do this automated. Preferably a command line instruction before I start my tests. After some searching I discovered …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More