为什么为使用ORDER by的选择打开游标不会反映对后续表的更新 [英] Why an cursor opened for a select with ORDER by does not reflect updates to the subsequent table

问题描述

我设置了这个奇怪行为的小例子。

  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; 
 
声明@tmp_id int，@tmp_value int; 
 declare tmpCursor cursor for 
 select t.id，t.value from #tmp t 
 --order by t.id; 
 
打开tmpCursor; 
 
 fetch next从tmpCursor into @tmp_id，@tmp_value; 
 
 while @@ FETCH_STATUS = 0 
 begin 
 print'ID：'+ cast（@tmp_id as nvarchar（max））; 
 
 if（@tmp_id = 1或@tmp_id = 2）
 insert into #tmp（value）
 values（@tmp_value * 10）; 
 
 fetch next从tmpCursor into @tmp_id，@tmp_value; 
 end 
 
 close tmpCursor; 
 deallocate tmpCursor; 
 
 select * from #tmp; 
 drop table #tmp;我们可以在 print 的帮助下观察。   游标如何解析 #tmp 表中的新行。但是，如果我们在游标声明中取消注释t.id 的顺序，则不会解析新行。 
 
 
 
这是一个预期的行为吗？
解决方案
是相当微妙。默认情况下，SQL Server中的游标是动态的，因此您可能会看到更改。但是，隐藏在文档中的行是：
 
 
如果select_statement中的子句
与所请求的
游标类型的功能冲突，SQL Server会将游标隐式转换为另一个类型。 p> 
 
当您包含 order by 时，SQL Server会读取所有数据，进入临时表进行排序。在此过程中，SQL Server还必须将游标的类型从动态更改为静态。这没有特别详细的记录，但你可以很容易地看到的行为。
 
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 ?
解决方案
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.

                        
这篇关于为什么为使用ORDER by的选择打开游标不会反映对后续表的更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！