Imagine you have a piece of code where you don’t care about the downsides to the “read uncommitted” isolation level, and do your due diligence by adding
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;at the start of your code. The code following that statement will run under that isolation level, as expected. However, if you call dynamic T-SQL with
sys.sp_executesql, and set the isolation level there, the dynamic code will run under the
READ UNCOMMITTED, however the isolation level will not be changed for the calling code. In other words, be careful about where you set the isolation level.
Click through for a demonstration of this.