WITH (NOLOCK) vs SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
sql, sql-server, sql-server-2005
Solution
They are the same thing. If you use the `set transaction isolation level` statement, it will apply to all the tables in the connection, so if you only want a `nolock` on one or two tables use that; otherwise use the other.
Both will give you dirty reads. If you are okay with that, then use them. If you can't have dirty reads, then consider `snapshot` or `serializable` hints instead.
Problem
Could someone give me some guidance on when I should use `WITH (NOLOCK)` as opposed to `SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED` What are the pros/cons of each? Are there any unintended consequences you've run into using one as opposed to the other?