Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Execution Plan Cursors


Execution Plan Cursors

Author
Message
Ken Wymore
Ken Wymore
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4452 Visits: 2343
Thomas Abraham (12/23/2013)
Wasn't really sure when I clicked 'submit'. I would have thought that KEYSET was a TYPE of cursor, and not the operator that generated the cursor.

For example (stolen from a 2007 Grant Fritchey post):

DECLARE CurrencyList CURSOR KEYSET FOR
SELECT CurrencyCode FROM [Sales].[Currency]
WHERE Name LIKE '%Dollar%'



But, since none of the other choices seemed correct, I went with it and was rewarded with another point I can put in my retirement account.



+1. I expected to somehow be wrong on this one although BOL pointed me at the right answer.
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21131 Visits: 18259
Ed Wagner (12/23/2013)
I liked the question, as it made me do research. Thank you, Steve.


+1



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5818 Visits: 7140
mtassin (12/23/2013)
Thomas Abraham (12/23/2013)

But, since none of the other choices seemed correct, I went with it and was rewarded with another point I can put in my retirement account.



What is the current SQL Server Central point conversion to US Dollar ratio these days anyway?


If memory serves, circa 100.000.000:1:-PSmooooth

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
pchirags
pchirags
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2172 Visits: 613
good question Steve.
Thanks
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8341 Visits: 11587
In all honesty - I do not like this question.

"Which of these operators creates a cursor" - define cursor?
The dictionary gives me two meanings, neither of which is appropriate here: the blinking symbol on my screen that points to where the next character will be entered, and the sliding part of a measuring instrument.
Within the context of SQL Server, there are a few common interpretations for this word.

* A specific language feature (both in ANSI SQL and in T-SQL with some additional features) that is used for row by row processing. These are not created by execution plan operators. Specific plan operators may be used by the optimizer to implement them - so you could say that a cursor "creates" these operators. But definitely not the other way around.
* Any T-SQL code that does row by row processing. This includes the standard CURSOR feature, but also includes other ways to fetch rows one at a time from a table. These, too, are not created by execution plan operators.
* Any form of internal row by row processing. When I first saw the question, I though this was the intended interpretation. And since all execution plans internally always process rows one by one (with the except of batch-enabled parts of a plan in SQL Server 2012 when columnstore indexes are involved), these "cursors" would be created by the topmost operators - and those are always SELECT, INSERT, UPDATE, DELETE, or MERGE. (Or OPEN CURSOR / FETCH CURSOR if you use T-SQL cursors in your code). None of these were available as answer options.
* Within relational theory, the term "cursor" is sometimes used to specify that the data returned is technically no longer a "set" (a bunch of data that is conceptually unordered and can hence be handled in any order), but ordered data that is supposed to be processed in the order it is produced. Relational theory purists say that a query that has an ORDER BY clause returns a cursor, and queries without ORDER BY cliase return a set. I cannot see any way to apply this definition to this question.

At this point, I knew I would have to disagree with the answer whatever it was. But I still wanted to answer, so I used my google-fu and found the article that is also referenced in the answer. And yes - Books Online does state that "The Snapshot operator creates a cursor (...)" - bad wording in Books Online! It also states that "The Keyset operator uses a cursor (...)" - hmmm, uses, not creates. But close enough, and a lot closer than the alternatives, so I went with it and got it right.

But I still don't like the question, I do not agree with Books Online, and I do not agree with the "correct" answer. None of these operators creates a cursor.


Oh, and for what it's worth - I also investigated when one would actually see these operators. Turns out, you can see them if:
1) you write code that used a T-SQL cursor; and
2) you look at the estimated execution plan of the DECLARE CURSOR statement.

You will not see them in the actual plan. In fact, you will not see an actual plan at all when executing a DECLARE CURSOR statement. The reason for that is simple: a DECLARE CURSOR statement will cause the optimizer to create up to two plans, tied together in the logical plan by one of the operators Snapshot, Dynamic, Fast Forward, or Keyset. These plans are not executed (hence no actual plan), but cached. When you then execute the OPEN CURSOR statement, the first part of the plan ("Population Query") will be executed. And each subsequent FETCH will then execute the second part of the plan ("Fetch Query"). If you first look at the estimated plan of the DECLARE CURSOR, then at the ]actual execution plan of the OPEN CURSOR and FETCH statements, you will see this.

So again: the Keyset and Snapshot operators do not create a cursor; they are created as a result of a cursor, and serve as an umbrella for the two cached plans that will be used for opening the cursor and for fetching its rows.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10769 Visits: 12019
I agree with Hugo here. But unlike him I thought the question wasn't worth doing any serious research on, so I didn't spot that "snapshot" had some reference to "cursor" in the documentation (and to me, a snapshot sounds like a spool, not a cursor, so I didn't consider it a possible answer). So I picked keyset (which sounds like a property of a cursor) plus the first of the other three options (excluding snapshot) , to see what the answer would be.
While the referenced page certainly says that the snapshot operator creates a cursor, I think Hugo is right to say that it's actually the other way round.

Tom

Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16526 Visits: 13210
Interesting question, thanks.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
sqlnaive
sqlnaive
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3695 Visits: 2774
Wow... Thats a good one. I don't use cursors because of some obvious reasons and hence have to do some r&d for it's each question. :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search