Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Execution Plan Cursors Expand / Collapse
Author
Message
Posted Monday, December 23, 2013 4:34 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 10:56 AM
Points: 3,417, Visits: 2,035
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.
Post #1525657
Posted Monday, December 23, 2013 8:25 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:20 PM
Points: 18,064, Visits: 16,099
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
Post #1525672
Posted Tuesday, December 24, 2013 1:19 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:51 AM
Points: 4,158, Visits: 5,556
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


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1525703
Posted Tuesday, December 24, 2013 11:46 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 11:55 AM
Points: 1,848, Visits: 590
good question Steve.
Thanks
Post #1525799
Posted Tuesday, December 24, 2013 3:25 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:02 PM
Points: 6,133, Visits: 8,396
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
Post #1525817
Posted Tuesday, December 24, 2013 7:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:21 PM
Points: 7,928, Visits: 9,653
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
Post #1525839
Posted Thursday, December 26, 2013 10:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 12:52 PM
Points: 13,636, Visits: 11,509
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1526010
Posted Sunday, December 29, 2013 11:30 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, November 20, 2014 4:29 AM
Points: 3,559, Visits: 2,671
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.
Post #1526391
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse