Is it possible to set DEFAULT TRANSACTION ISOLATION for all connections in SQL Server?

Question

I want the default Transaction Isolation level to be SNAPSHOT.

How do I set this on a SQL Server 2016 database?

Answer 1

It is not possible to configure Snapshot Isolation (SI) as the default isolation level.

To use SI, the database must be enabled for Snapshot Isolation:

ALTER DATABASE CURRENT
SET ALLOW_SNAPSHOT_ISOLATION ON;

Then each connection must explicitly request SI, using for example:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

It is possible to set the default Read Committed isolation level to use row versioning (RCSI).

If Read Committed Snapshot Isolation (RCSI) is sufficient for your purposes, the setting is:

ALTER DATABASE CURRENT
SET READ_COMMITTED_SNAPSHOT ON; 

Further reading:

• Snapshot Isolation in SQL Server (documentation)
• How to set the default transaction isolation level server wide? by Nacho Alonso Portillo