When will a FAST_FORWARD cursor have a work table (and is this something to avoid)?

Question

Just a hunch, but normally a TOP-ORDER BY requires SQL Server to buffer the result in some way (either the index scan's result or indeed the entire result in a temp structure, or anything in between).

One could argue that for cursors this is also necessary even when ordering by the primary key (as in your example), as you cannot allow a TOP 5 cursor to unexpectedly return less than 5 rows when the corresponding SELECT does return exactly 5 rows (or worse: the cursor returns more than 5 rows).

This weird situation could theoretically happen when there are deletes or inserts on the table after the index scan's range has already been determined for the cursor, and the inserts/deletes fall within the index scan's range, but you're not yet done fetching. To prevent this from happening, they might err on the safe side here. (And they just didn't optimize for #temp tables.)

A question though: does SQL Server allow a FETCH FROM SELECT TOP n without an ORDER BY clause? (Haven't got a SQL Server instance running here.) Might be interesting to know what plan that causes.

What criteria does SQL Server use in choosing one approach over the other?

It is primarily a cost-based decision. Quoting from the article you linked to, "In situations where the dynamic plan looks promising, the cost comparison may be heuristically skipped. This occurs mainly for extremely cheap queries, though the details are esoteric."

Would I be right in thinking that the first method (without the additional work table population step) is more efficient?

It depends. Dynamic and static cursor plans have different strengths and weaknesses. If all rows will eventually be touched, the static plan is likely to perform better. More on this in a moment.

It is clear that the TOP construct is not supported for dynamic cursors

This is true. All iterators in a dynamic cursor plan must be able to save and restore state, scan forward and backward, process one input row for each output row, and be non-blocking. Top, in general, does not satisfy all these requirements; the class CQScanTopNew does not implement the necessary Set/Get/Goto/Marker(), and ReverseDirection() methods (among others).

I have also read that dynamic cursors tend to be slower than their static counterparts.

This is often true, for Transact-SQL cursors, where most or all of the cursor set is touched. There is a cost associated with saving and restoring the state of a dynamic query plan. Where a single row is processed on each call, and all rows eventually touched, this save/restore overhead is maximized.

Static cursors have the overhead of making a copy of the set (which may be the dominant factor for a large set), but the per-row cost of retrieval is quite small. Keysets have a higher per-row retrieval overhead than static because they must outer join back to the source tables to retrieve non-key columns.

Dynamic cursors are optimal when a relatively small fraction of the set is accessed, and/or retrieval is not row-at-a-time. This is a typical access pattern in many common cursor scenarios, just not the ones blog posts tend to test :)

If anyone could explain why each scan in the first query counts as 2 logical reads that might be quite enlightening too

This is down to the way state is saved for the scan, and the way reads are counted.

The article I referenced earlier mentions that dynamic cursors use markers. Can anyone explain what these are? Is it just a RID or the CI key, or something different?

Markers exist for each iterator in a dynamic cursor plan, not just access methods. The 'marker' is all the state information necessary to restart the plan iterator at the point it left off. For an access method, an RID or index key (with uniquifier if necessary) is a big part of this, but not the whole story by any means.