• In an effort to further explain what occurs with the SNAPSHOT and READ COMITTED Isolation using Row versioning - the following quotes were taken from

    http://technet.microsoft.com/en-us/library/ms345124(SQL.90).aspx. The use of italics and bold facing were added by this poster.

    Under the heading

    Snapshot Isolation (Transaction-Level Read Consistency)

    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)

    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.[/b] 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

    Hope this clarifies the situation

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]