Why an cursor opened for a select with ORDER by does not reflect updates to the subsequent table

Question

I have set this little example of a strange behavior

    SET NOCOUNT ON;
    create table #tmp
    (id int identity (1,1),
    value int);

    insert into #tmp (value) values(10);
    insert into #tmp (value) values(20);
    insert into #tmp (value) values(30);

    select * from #tmp;

    declare @tmp_id int, @tmp_value int;
    declare tmpCursor cursor for 
    select t.id, t.value from #tmp t
    --order by t.id;

    open tmpCursor;

    fetch next from tmpCursor into @tmp_id, @tmp_value;

    while @@FETCH_STATUS = 0
    begin
        print 'ID: '+cast(@tmp_id as nvarchar(max));

        if (@tmp_id = 1 or @tmp_id = 2)
            insert into #tmp (value)
            values(@tmp_value * 10);

        fetch next from tmpCursor into @tmp_id, @tmp_value;
    end

    close tmpCursor;
    deallocate tmpCursor;

    select * from #tmp;
    drop table #tmp;

We can observe with the help of print how the cursors parses even the new rows in the #tmp table. However if we uncomment the order by t.id in the cursor declaration - the new rows are not parsed.

Is this an intended behavior ?

Answer 1

The behavior you see is rather subtle. By default, cursors in SQL Server are dynamic, so you would expect to see changes. However, buried in the documentation is this line:

SQL Server implicitly converts the cursor to another type if clauses in select_statement conflict with the functionality of the requested cursor type.

When you include the order by, SQL Server reads all the data and turns it into a temporary table for sorting. In this process, SQL Server must also change the type of cursor from dynamic to static. This is not particularly well documented, but you can readily see the behavior.

Answer 2

You can use the sp_describe_cursor stored procedure to view the metadata of the cursor. Doing so on your example shows the following:

ORDER BY included:

model = Insensitive (or static), concurrency = Read-Only

ORDER BY excluded:

model = Dynamic, concurrency = Optimistic

Source: http://technet.microsoft.com/en-us/library/ms173806(v=sql.105).aspx