Why an cursor opened for a select with ORDER by do

问题:

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 ?

回答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.

回答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

回答3:

I think that by putting in an ORDER BY clause it then forces the CURSOR to be a STATIC CURSOR whereas without it defaults to DYNAMIC.