SQLServerCentral.com / Discuss Content Posted by Steve Jones / Article Discussions / Article Discussions by Author / Execution Plan Cursors / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 31 Mar 2015 15:21:42 GMT20RE: Execution Plan Cursorshttp://www.sqlservercentral.com/Forums/Topic1525288-32-1.aspxWow... 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. :-)Sun, 29 Dec 2013 23:30:38 GMTsqlnaiveRE: Execution Plan Cursorshttp://www.sqlservercentral.com/Forums/Topic1525288-32-1.aspxInteresting question, thanks.Thu, 26 Dec 2013 10:58:50 GMTKoen VerbeeckRE: Execution Plan Cursorshttp://www.sqlservercentral.com/Forums/Topic1525288-32-1.aspxI 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.Tue, 24 Dec 2013 19:14:56 GMTTomThomsonRE: Execution Plan Cursorshttp://www.sqlservercentral.com/Forums/Topic1525288-32-1.aspxIn 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 [b]internal[/b] 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 [b]creates[/b] a cursor (...)" - bad wording in Books Online! It also states that "The Keyset operator [b]uses[/b] 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 [i]see[/i] these operators. Turns out, you can see them if:1) you write code that used a T-SQL cursor; and2) you look at the [i]estimated[/i] 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 [i]]actual[/i] 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.Tue, 24 Dec 2013 15:25:16 GMTHugo KornelisRE: Execution Plan Cursorshttp://www.sqlservercentral.com/Forums/Topic1525288-32-1.aspxgood question Steve. ThanksTue, 24 Dec 2013 11:46:44 GMTpchiragsRE: Execution Plan Cursorshttp://www.sqlservercentral.com/Forums/Topic1525288-32-1.aspx[quote][b]mtassin (12/23/2013)[/b][hr][quote][b]Thomas Abraham (12/23/2013)[/b][hr]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.[/quote]What is the current SQL Server Central point conversion to US Dollar ratio these days anyway?[/quote]If memory serves, circa 100.000.000:1:-P:smooooth:Tue, 24 Dec 2013 01:19:50 GMTStewart "Arturius" CampbellRE: Execution Plan Cursorshttp://www.sqlservercentral.com/Forums/Topic1525288-32-1.aspx[quote][b]Ed Wagner (12/23/2013)[/b][hr]I liked the question, as it made me do research. Thank you, Steve.[/quote]+1Mon, 23 Dec 2013 20:25:58 GMTSQLRNNRRE: Execution Plan Cursorshttp://www.sqlservercentral.com/Forums/Topic1525288-32-1.aspx[quote][b]Thomas Abraham (12/23/2013)[/b][hr]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):[code="sql"]DECLARE CurrencyList CURSOR KEYSET FORSELECT CurrencyCode FROM [Sales].[Currency]WHERE Name LIKE '%Dollar%'[/code]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.[/quote]+1. I expected to somehow be wrong on this one although BOL pointed me at the right answer.Mon, 23 Dec 2013 16:34:34 GMTKWymoreRE: Execution Plan Cursorshttp://www.sqlservercentral.com/Forums/Topic1525288-32-1.aspx[quote][b]Thomas Abraham (12/23/2013)[/b][hr]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.[/quote]What is the current SQL Server Central point conversion to US Dollar ratio these days anyway?Mon, 23 Dec 2013 09:22:05 GMTmtassinRE: Execution Plan Cursorshttp://www.sqlservercentral.com/Forums/Topic1525288-32-1.aspxCan I get 1 point for getting 1 right?Mon, 23 Dec 2013 07:42:49 GMTssimmons 2102RE: Execution Plan Cursorshttp://www.sqlservercentral.com/Forums/Topic1525288-32-1.aspxWasn'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):[code="sql"]DECLARE CurrencyList CURSOR KEYSET FORSELECT CurrencyCode FROM [Sales].[Currency]WHERE Name LIKE '%Dollar%'[/code]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.Mon, 23 Dec 2013 06:44:18 GMTThomas AbrahamRE: Execution Plan Cursorshttp://www.sqlservercentral.com/Forums/Topic1525288-32-1.aspxI liked the question, as it made me do research. Thank you, Steve.Mon, 23 Dec 2013 05:41:20 GMTEd WagnerRE: Execution Plan Cursorshttp://www.sqlservercentral.com/Forums/Topic1525288-32-1.aspxvery good start of the week and QotD :-)Thanks for sharing Steve.Mon, 23 Dec 2013 02:43:51 GMTtwin.devilRE: Execution Plan Cursorshttp://www.sqlservercentral.com/Forums/Topic1525288-32-1.aspx[quote][b]Hany Helmy (12/21/2013)[/b][hr]If it`s going to make you dig `n` search, then it`s a good question (most probably).Thanks Steve.[/quote]+1 Mon, 23 Dec 2013 00:57:52 GMTLokesh VijRE: Execution Plan Cursorshttp://www.sqlservercentral.com/Forums/Topic1525288-32-1.aspxSteve, good question. Thanks.Sun, 22 Dec 2013 06:13:12 GMTsteve.jacobsRE: Execution Plan Cursorshttp://www.sqlservercentral.com/Forums/Topic1525288-32-1.aspxIf it`s going to make you dig `n` search, then it`s a good question (most probably).Thanks Steve.Sat, 21 Dec 2013 23:34:58 GMTHany HelmyRE: Execution Plan Cursorshttp://www.sqlservercentral.com/Forums/Topic1525288-32-1.aspxNice one, thanks, SteveSat, 21 Dec 2013 12:51:52 GMTStewart "Arturius" CampbellExecution Plan Cursorshttp://www.sqlservercentral.com/Forums/Topic1525288-32-1.aspxComments posted to this topic are about the item [B]<A HREF="/questions/Execution+Plans/105360/">Execution Plan Cursors</A>[/B]Sat, 21 Dec 2013 12:50:49 GMTSteve Jones - SSC Editor