WITH (NOLOCK) vs SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

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?

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.

WITH (NOLOCK) is a hint on a table level. Setting the transaction isolation level to READ_UNCOMMITTED with affect the connection. The difference is in terms of scope. See READUNCOMMITTED and NOLOCK in the SQL Server documentation here:

http://technet.microsoft.com/en-us/library/ms187373.aspx

For TRANSACTION ISOLATION LEVEL: http://technet.microsoft.com/en-us/library/ms173763.aspx

• NOLOCK is local to the table (or views etc)
• READ UNCOMMITTED is per session/connection

As for guidelines... a random search from StackOverflow and the electric interweb...

• Is the NOLOCK (Sql Server hint) bad practice?
• When is it appropriate to use NOLOCK?
• Get rid of those NOLOCK hints…