I want the default Transaction Isolation level to be SNAPSHOT.
How do I set this on a SQL Server 2016 database?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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;