Finding the default isolation level in SQL Server 2017 and Azure SQL DB

Question

I'm reading a book related to transaction and concurrency. In one paragraph it is mentioned that:

• In on-premises SQL Server instance the default isolation level is Read-Committed based on locking

And the next sentence is:

• The default in SQL Database is Read - committed snapshot based on row-versioning

My question is: what is the difference between "On-premises SQL Server instance" and "SQL Database" in these two sentences?

What is the default isolation level and how can I find it? Is there any special query to find out the default isolation level?

Answer 1

It's referring to Azure SQL Database which uses RCSI by default.

Isolation Level SQL

Database default database wide setting is to enable read committed snapshot isolation (RCSI) by having both the READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION database options set to ON, learn more about isolation levels here. You cannot change the database default isolation level. However, you can control the isolation level explicitly on a connection.

In on-premises SQL Server, the default isolation level is Read Committed.

Finding the "default" isolation level is a bit of a goose chase. It will depend on the database where a query is initiated from, and if any hints exist in the query to override a database level setting.

That said, you could query sys.databases to find out if RCSI or SI is enabled.

SELECT d.name, 
       d.is_read_committed_snapshot_on, 
       d.snapshot_isolation_state
FROM sys.databases AS d;

Note that the READ_COMMITTED_SNAPSHOT is not technically an isolation level. Rather, it is a database option that controls the implementation so that row versioning instead of locking is used to provide read consistency for sessions using the READ_COMMITTED isolation level.