SQL Server 2005 introduces a feature called Row Level Versioning which allows effective management of concurrent access to the data while maintaining the consistency of data. Usually an isolation level decides an extent on how the modified data is isolated from other. RLV benefits when data is accessed across isolation levels because they help to eliminate the read operation locks, thus improving the read concurrency. The fact is that the read operations do not require shared locks on the data when it is running on isolation levels with RLV, this eventually does not block other requests accessing the same data, and that is how the locking resources are minimized. On the other hand, when it comes to write operations, two write requests cannot modify the same data at the same time.

Triggers working on INSERT and DELETE operations change the versions of the rows. So triggers that modify data will benefit from RLV. The rows of the result set is versioned, when an INSERT, DELETE or UPDATE statement is issued prior the data was accessed using the SELECT statement.

Transactions greatly affect the data when you perform CRUD operations. Transactions may also be executed in batch with many requests operating on a single or a row set. So when a transaction modifies a row value the previous committed row value is stored as version in tempdb.

By setting the READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION options to ON, logical copies are made on the modified data by the transactions, and a transaction sequence number is assigned to every transaction that operates on the data using Row Level Versioning. The transaction sequence number is automatically incremented each time the BEGIN TRANSACTION statement is executed.

So the changes to the row are marked with transaction sequence numbers. These TSN's are linked with the newer rows that reside in the current database. Now, the TSN's are monitored periodically and numbers with least use are deleted from time to time by the database. So it is up to the database which actually decides how long the row versions have to be stored in the tempdb database.

Now, the transaction sequence numbers are tracked periodically and transaction sequence numbers with least use are deleted from time to time. As a matter of fact, the read operations do not require shared locks on the data when it is running on isolation levels with Row Level Versioning, which eventually does not block other readers or writers accessing the same data, as a result the locking resources are minimized. On the other hand, when it comes to write operations, two writers cannot modify the same data at the same time.

The READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION options should be turned on in order that the transaction isolation levels such as READ_COMMITTED and SNAPSHOT make use of the RLV system. The read committed isolation level supports distributive transactions unlike the snapshot which does not. The temporary database "tempdb" is extensively used by the SQL Server to store its temporary result sets. All the versions are stored in the tempdb database. Once the database has exceeded its maximum space utilization the update operations stops generating versions. The applications that leverage the row committed level transactions does not even need to be re-factored for enabling the RLV and also it consumes less storage space of the tempdb database, and for these reasons the read committed isolation level is preferred over the snapshot isolation.

Row Level Versioning help in situations where an applications lot of insert and update operations on the data and at the same time a bunch of reports are accessing in parallel. It could also prove beneficial if your server is experiencing relatively high deadlocks. Also for systems performing mathematical computation, they require accurate precision and RLV gives a greater amount of accuracy for such kind of operations.