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?


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

Isolation Level SQL


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, 
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.

Your Answer

