The SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is at different part of the “BEGIN”. Is the below two query behave the same?
Will the views, viewOne and viewTwo, inherit the dirty read? Or the view needs to have an explicit “NOLOCK”
set transaction isolation level read uncommitted begin select col1 from viewOne select * from viewTwo select * from table1 end
begin set transaction isolation level read uncommitted select col1 from viewOne select * from viewTwo select * from table1 end
From this documentation – SET TRANSACTION ISOLATION LEVEL – under REMARKS
Only one of the isolation level options can be set at a time, and it
remains set for that connection until it is explicitly changed. All
read operations performed within the transaction operate under the
rules for the specified isolation level unless a table hint in the
FROM clause of a statement specifies different locking or versioning
behavior for a table.
You can use this query to determine the isolation level you are currently at:
SELECT CASE transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'ReadUncommitted' WHEN 2 THEN 'ReadCommitted' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL FROM sys.dm_exec_sessions where session_id = @@SPID