When it comes to learning any technology and advancing to a more advanced level, it is crucial to understand the fundamentals of the subject first. In the world of SQL Server, one important concept that is often overlooked is the isolation level, specifically Snapshot Isolation.

Snapshot Isolation was introduced in SQL Server in 2005, but many software shops are still using older versions of SQL Server, such as SQL Server 2000, which do not support Snapshot Isolation. Even for those who have upgraded to newer versions, many developers have not taken the time to familiarize themselves with this powerful feature.

Snapshot Isolation is often misunderstood and confused with Snapshot Replication. In reality, Snapshot Isolation is a feature that allows for optimistic concurrency control. It maintains updated row versions for each transaction in TempDB and ignores any newer rows inserted or updated in the table once a transaction has begun. This reduces blocking and allows for concurrent reading and writing transactions.

Let’s take a look at a simple demonstration to understand how Snapshot Isolation works:

-- Enable Snapshot Isolation for the database
ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON

-- Check the existing values in the table
SELECT ModifiedDate FROM HumanResources.Shift

-- Session 1
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
UPDATE HumanResources.Shift SET ModifiedDate = GETDATE ()

-- Session 2
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
SELECT ModifiedDate FROM HumanResources.Shift

-- Session 1
COMMIT

-- Session 2
SELECT ModifiedDate FROM HumanResources.Shift

-- Session 2
COMMIT

SELECT ModifiedDate FROM HumanResources.Shift

In this example, we enable Snapshot Isolation for the AdventureWorks database and then perform two separate transactions in different sessions. In Session 1, we update the “ModifiedDate” column of the HumanResources.Shift table. In Session 2, we select the “ModifiedDate” from the same table. We can observe that the values in the table remain unchanged until the transaction in Session 1 is committed. Once committed, the updated values are visible in Session 2.

Snapshot Isolation provides a powerful mechanism for managing concurrency in SQL Server. It allows for non-blocking reads and writes, improving performance and reducing contention. However, it is important to thoroughly test the performance impact of using Snapshot Isolation in your production environment before implementing it.

In a future post, we will discuss another level of isolation, READ_COMMITTED_SNAPSHOT, which offers a different approach to managing concurrency in SQL Server.

Thank you for reading and I hope this example has provided you with a clear understanding of how Snapshot Isolation works in SQL Server.