Is it possible to set TRANSACTION ISOLATION LEVEL SNAPSHOT automatically?

because i've recognized locks on tables that are queried simultaneously, i've decided to enable ISOLATION LEVEL SNAPSHOT that was new to me.

ALTER DATABASE RM2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE ;
ALTER DATABASE RM2 SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE RM2 SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE RM2 SET MULTI_USER;

But now i'm wondering if it开发者_Go百科 is possible to apply this Isolation level on all existing queries automatically because this DB contains lots of Views,Stored Procedures, Table Values Functions and is queried by many different applications(e.g. SSRS,SSAS,ASP.NET with ADO.NET,ADO Connections from Excel).

If i've understood correctly i have to modify the queries to use IsolationLevel.Snapshot, for example:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
GO
BEGIN TRAN SparePartReport
SELECT TOP 100 PERCENT * FROM DBO.Last3MonthSparepartReport
COMMIT TRAN SparePartReport

Q: Is it possible in some way to minimize the effort because i cannot change all applications and queries. Another problem seems to be that i cannot apply the IsoalationLevel on Views directly.

Note: Most of the tables in the database that are relevant are read-only all the day, because the data is imported overnight by a Windows-Service and Stored-Procedures.

Thanks in advance

---

Although @Maximilian's answer seems to be the correct, i'm still having deadlock/timout situations in the ASP.NET application when running large reports simultaneously. Here is the followup question.

---

You don't have to use ISOLATION LEVEL SNAPSHOT in order to use row-versioning instead of locks. You can just use

ALTER DATABASE RM2 SET READ_COMMITTED_SNAPSHOT ON;

to tell your database that it should use row-versioning with ISOLATION LEVEL READ COMMITTED. And because that's the default isolation level, all your current queries that don't specify a different isolation level will than automatically use row-versioning.

For reference: MSDN

---

Reading from the database uses snapshot isolation automatically if the database has READ_COMMITTED_SNAPSHOT ON and the transaction isolation level is READ_COMMITTED (the default).

However, updates still use locks during the read portion of the statement unless the transaction isolation level is SNAPSHOT. You can't tell the database to change the default transaction isolation level, since it's scoped to a connection, not a database. You need to SET TRANSACTION ISOLATION LEVEL SNAPSHOT or use one of the other means of customizing transaction isolation level on a per-connection basis.