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.