May 22, 2013 at 9:38 am
First.... let's just assume i know sets should be used instead of cursors in most cases, and skip all the comments on that.
I'm more wondering what would actually happen in this case.
Assume we have views created and on each of these views is a no-lock.
Cursors lock the tables/views they are using if i'm not incorrect, (one of the reasons not to use a cursor)
but what happens with no-lock views, and a cursor?
is a lock obtained? or is it ignored?
i couldn't find anything specific on this one.
Thoughts other than, 'use sets and not cursors'?
May 22, 2013 at 9:49 am
richard.noordam (5/22/2013)
Cursors lock the tables/views they are using if i'm not incorrect, (one of the reasons not to use a cursor)
Not necessarily.
The reason not to use cursors is because cursors are a row-by-row processing method and slower than set-based code in most cases.
but what happens with no-lock views, and a cursor?
No shared locks are taken on the underlying tables. Other locks may be taken as necessary (nolock just means no shared locks taken for reading)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 22, 2013 at 9:49 am
richard.noordam (5/22/2013)
First.... let's just assume i know sets should be used instead of cursors in most cases, and skip all the comments on that.
Cursors are absolutely fine where relevant.
Assume we have views created and on each of these views is a no-lock.
If you mean NOLOCK hint was used, then it's definitely not a good idea.
Cursors lock the tables/views they are using if i'm not incorrect, (one of the reasons not to use a cursor)
"set" operations (eg. SELECT) also can and will lock the table/view.
It's not the reason why you don't want to use a cursor where it's possible to achieve functionality by "set" operation.
Just in case, have you heard about READ_ONLY cursors?
but what happens with no-lock views, and a cursor?
is a lock obtained? or is it ignored?
i couldn't find anything specific on this one.
It depends on how the cursor was declared, its underlying query and what happens inside of cursor.
Thoughts other than, 'use sets and not cursors'?
Use brains 😉
May 22, 2013 at 11:10 am
i actually didn't use such a solution, but did use a table variable and a coalesce statement to acheive the same thing.
I was, however, curious as to it's effect.
Apparently that was lost on some who'd rather make smart alec comments.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply