0

Let's say I have a view within an enterprise application. This view is used to build a data set, and it will be called from a stored procedure and eventually return the data set to the C# ASP.NET MVC layer.

What is the difference between adding this code once, at the top of the view:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

vs adding WITH READ UNCOMMITTED or WITH (NOLOCK) on every table like this:

SELECT * 
FROM State WITH (READUNCOMMITTED) 
JOIN City WITH (READUNCOMMITTED) ON City.StateID = State.StateID
JOIN Street WITH (READUNCOMMITTED) ON Street.CityID = City.CityID
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Pringle22
  • 31
  • 3
  • Possible duplicate of [Why use a READ UNCOMMITTED isolation level?](https://stackoverflow.com/questions/2471055/why-use-a-read-uncommitted-isolation-level) – BPDESILVA Jul 17 '19 at 03:18
  • See [Bad Habits to kick - putting NOLOCK everywhere](https://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/) - it is *not recommended* to use this everywhere - quite the contrary! – marc_s Jul 17 '19 at 04:14

1 Answers1

2

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED doesn't become part of the view. That simply sets the isolation level for you current connection. Anyone using the view won't have that setting applied.

AaronHolland
  • 1,595
  • 1
  • 16
  • 32
  • 1
    To make this more clear - you CANNOT set the isolation level in a view. A view consists of a single sql statement and the "set transaction isolation level ..." approach requires 2 statements. – SMor Jul 17 '19 at 13:02