How to change the default TRANSACTION ISOLATION LEVEL in SQL Server 2005?

Question

I know the default TRANSACTION ISOLATION LEVEL in SQL Server is "read committed". If I want to change it to "READ UNCOMMITTED", how may i make this configuration change?

note: I cannot use SET TRANSACTION ISOLATION LEVEL, which only apply in the current session. I cannot add NOLOCK in the queries because there are thousands of queries involved.

Thanks

---

Thanks for your answer. We are ok with reading dirty rows. Update is not a problem in our case as well. but, I really want to change this default config of isolation level. Please kindly help.

I cannot "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED". I must make the global change.

---

We have carefully reviewed both snapshot isolation level. They cannot be used in our situation.

Answer 1

For SQL Server, you can only set transaction isolation level on session(connection) level but not on global level different from MySQL. So, every time you log out SQL Server, transaction isolation level is reset to READ COMMITTED which is default transaction isolation level.

Answer 2

I really do not think you should set that at a global level. You should be setting this pretty carefully because you can end up with a lot of different problems:

• Lost Updates
• Non repeatable reads
• Dirty reads
• Phantom reads

There is no way to set this at a database or a server level - it can only be set at a connection level.

The best you can do at a database level is to set the ALLOW_SNAPSHOT_ISOLATION or READ_COMMITTED_SNAPSHOT properties. Read more here:

http://msdn.microsoft.com/en-us/library/tcbchxcb%28VS.80%29.aspx

Answer 3

What about:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

It will only apply to the current transaction.