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

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.

Leave a Comment