Benefits of SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
It is terrible, that you learned it that way (sorry!).
READ UNCOMMITTED
let's you read every row, yes. Even those who are currently used in an INSERT
, UPDATE
, DELETE
operation. This is very useful if you need to take a quick look at some Data or in mission critical SELECT
-Statements where a block would be very harmful.
In fact, you risk your integrity. It may occur that you read a row, which is currently used to be deleted or in a change. It can also appear that you read a wrong value. This may be really uncommon, but it can happen. What do I mean with that? Well, think of a row which is very broad (it has many columns with many long nvarchar
-columns). An update occur on this row and sets new values. In rare cases it can happen to you, that you read only a half row. Another thing can happen for example, if a user changes his login values. He changes his mail + password. The mail is already set, but the password isn't. This way you have a inconsistent state.
I would suggest to forget about READ UNCOMMITTED
. Just use it where it's really needed.
Another alternative for you can be to enable the READ_COMMITTED_SNAPSHOT
database option - therefor you can use READ COMMITTED SNAPSHOT
due to the enabled row versioning in your tempdb. This way you just read another (older) version of a row. This won't block your Queries. But it may occur that you read an old value too, but an consistent old value.
Another idea can be WITH(READPAST)
instead of WITH(NOLOCK)
. You will read the old state of the table (a bit like in the SNAPSHOT ISOLATION
), but you'll skip all currently locked rows instead.
As the accepted answer states, forget about using READ UNCOMMITTED isolation level (except when really needed) as you risk reading wrong data. But to answer the 3rd bullet point in your question there are two situations I find useful:
When writing and testing SQL Server SSIS packages, the steps of the package may be wrapped in a transaction. If you are testing a package by running it step by step in the SSIS debugger, you may want to examine tables while there are locks on the table. Using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED allows you to use SQL Server Manager Studio to examine the tables while the package is being debugged.
In SQL Server Manager Studio you may want to test T-SQL code by wrapping it in a transaction to give you the option of rolling back the changes. For example on a test database you may want to restore data to the initial state as part of your test. If you are testing transaction-wrapped code and you want to check locked tables while the transaction is underway, you can use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED to examine the values in another window.
I accept these are fairly obscure uses for READ UNCOMMITTED, but I have found them useful in a test environment.