Using SET ISOLATION LEVEL

Question

Using SET ISOLATION LEVEL

Christopher Stobbs

SSC-Insane

Points: 21098
More actions
June 11, 2008 at 5:47 am

#187864

Hi All,
a quick question here.
What are the pros and cons of:
USING
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
VS
WITH (NOLOCK)
VS
WITH (READUNCOMMITTED)
I always thought that you should stay away from SET commands if possible and then the WITH options are the same?
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
SQL-4-Life

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

See my post here, for some background... http://www.sqlservercentral.com/Forums/Topic514855-1291-1.aspx#bm515159

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 2

Thanks for that.

What I really want to know is:

IS there a major difference between controlling the Isolation using a SET statement or using a HINT?

thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

At this point I am not sure. I would have to research it, but hopefully, someone else here know the answer and will chime in.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 4

thanks rbarryyoung

I'll wait and see if anyone comes up with someone while I'll researching this a bit more 🙂

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 5

HI All,

I was wondering if anyone had found anything on this?

I've been looking but can't see to find anything to read up 🙁

I'm sure I read somewhere a year ago or so that using a hint was better than using a set statement :hehe:

thanks in advance again

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Vladan SSC-Insane Points: 22072 More actions · Answer 6

As far as I know, the main difference is, that SET makes this setting valid for all subsequent operations (until it is re-set or connection closed), while WITH(...) sets the behavior not only for just one query, but also on table level. You can use this hint with part of the tables used in a query, and the other tables in the same query will be unaffected.

I prefer to use WITH(NOLOCK), because it can't propagate somewhere where I didn't want to use it. I have used SET once or twice in stored procedures, when I had several complex queries in the procedure, and didn't want to write WITH(NOLOCK) so many times... but it was really more because of laziness than anything else.