Different Result Select vs. Cursor

  • Hello everybody,

    In the last days I found a T-SQL miracle (maybe only for me - I hope so)

    I don't understand the different result of these two (similar) statements - maybe there is somebody who can explain me

    set nocount on

    -- Example for different Result

    -- I insert the result into Temp-Tables

    -- my static select

    begin try drop table #select end try begin catch end catch

    select a.name a_name, b.name b_name

    into #select

    from sys.schemas a

    full outer join

    (select name from (values ('dbo')) [values] (name)) b on a.name = b.name

    -- create a second table for the cursor result

    begin try drop table #cursor end try begin catch end catch

    select * into #cursor from #selectwhere 1 = 0

    -- same statement only as DYNAMIC cursor

    DECLARE test_cursor cursor DYNAMIC FOR

    select a.name a_name, b.name b_name

    from sys.schemas a

    full outer join

    (select name from (values ('dbo')) [values] (name)) b on a.name = b.name

    open test_cursor

    DECLARE @a_namesysname, @b_namesysname;

    fetch test_cursor into @a_name, @b_name

    while (@@fetch_status = 0)

    begin

    insert into #cursor values ( @a_name, @b_name)

    fetch test_cursor into @a_name, @b_name

    end

    close test_cursor

    deallocate test_cursor

    -- different result for guest --> why b_name is 'dbo' in #cursor

    -- for better overview only of 2 schemas

    select * from #select where a_name in ('dbo','guest')

    select * from #cursor where a_name in ('dbo','guest')

    And if I put a "Where b_name is null" into the Select statement it is completly absurd - I get rows with 'dbo' in b_name

    But only rows where - for my opinion - the result should be NULL

    select a.name a_name, b.name b_name

    from sys.schemas a

    full outer join

    (select name from (values ('dbo')) [values] (name)) b on a.name = b.name

    where b_name is null

    And I know this phenomenon is only in dynamic cursors - but why - I don't change the derived tables during execution

    I am awaiting your answer.

    Gerhard P.Advanced BI DeveloperAustria

  • I'm don't know why it is doing what it is doing, but I can say that if you look at the execution plans for the simple select vs. the cursor the plans are completely different. The cursor plan never actually has a FULL OUTER JOIN operation, it has a series of left join operations and a concatenation operator that somehow populates b_name with 'dbo' for every row.

  • Weird.

    If you make it "FORWARD_ONLY" and "READ_ONLY" (or FAST_FORWARD) then it works as expected (the execution plan has the full outer join and results are as expected).


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Is it because its defined as DYANAMIC and BoL states that the data can change within the cursor during the loop.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • What you mean with BoL States?

    I also think, that it is in case of dynamic cursor.

    But I don't understand it.

    I am alone on the system. I don't think, that sql server change something in sys.schemas ?!

    And if?

    Why he gives me 'dbo' in b_name? Full outer join should bring me only a 'dbo' in case of left side 'dbo'.

    If you try to select 2 values on the right side ('dbo', 'xyz') it works as estimated.

    Also if you use' select 'dbo' in case of values.

    I hope there is anybody out there in the "sql server jungle" who can explain me this behaviour.

    Gerhard P.Advanced BI DeveloperAustria

  • Avoid cursors. If you can get same result via pure TSQL, that will always perform better. In fact, getting rid of cursors will help you think in sets.

    Regarding your question though and while not 100% sure, it may be due isolation level that the dynamic cursor uses, which seems to be different than your regular sql solution.

  • hudriwudri5 (4/15/2015)


    What you mean with BoL States?

    BoL (books on Line) : taken from the page https://msdn.microsoft.com/en-GB/library/ms180169.aspx

    DYNAMIC

    Defines a cursor that reflects all data changes made to the rows in its result set as you scroll around the cursor. The data values, order, and membership of the rows can change on each fetch. The ABSOLUTE fetch option is not supported with dynamic cursors.

    I also think, that it is in case of dynamic cursor.

    It does Change the nature of the query, Run the same query with the keyword STATIC and see what happens.

    set nocount on

    -- Example for different Result

    -- I insert the result into Temp-Tables

    -- my static select

    begin try drop table #select end try begin catch end catch

    select a.name a_name, b.name b_name

    into #select

    from sys.schemas a

    full outer join

    (select name from (values ('dbo')) [values] (name)) b on a.name = b.name

    -- create a second table for the cursor result

    begin try drop table #dynamic_cursor end try begin catch end catch

    select * into #dynamic_cursor from #selectwhere 1 = 0

    begin try drop table #static_cursor end try begin catch end catch

    select * into #static_cursor from #selectwhere 1 = 0

    -- same statement only as DYNAMIC cursor

    DECLARE test_cursor cursor DYNAMIC FOR

    select a.name a_name, b.name b_name

    from sys.schemas a

    full outer join

    (select name from (values ('dbo')) [values] (name)) b on a.name = b.name

    open test_cursor

    DECLARE @a_namesysname, @b_namesysname;

    fetch test_cursor into @a_name, @b_name

    while (@@fetch_status = 0)

    begin

    insert into #dynamic_cursor values ( @a_name, @b_name)

    fetch test_cursor into @a_name, @b_name

    end

    close test_cursor

    deallocate test_cursor

    -- same statement only as DYNAMIC cursor

    DECLARE test_cursor cursor STATIC FOR

    select a.name a_name, b.name b_name

    from sys.schemas a

    full outer join

    (select name from (values ('dbo')) [values] (name)) b on a.name = b.name

    open test_cursor

    DECLARE @a_name2sysname, @b_name2sysname;

    fetch test_cursor into @a_name2, @b_name2

    while (@@fetch_status = 0)

    begin

    insert into #static_cursor values ( @a_name2, @b_name2)

    fetch test_cursor into @a_name2, @b_name2

    end

    close test_cursor

    deallocate test_cursor

    SELECT * FROM #dynamic_cursor

    SELECT * FROM #static_cursor

    The Data in the #static_cursor table is notably different from that in the #dynamic_cursor, and matches the data in the #select.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • Of course, the dynamic changes the nature of the query!

    I am sure, there is a simple answer, but why we get

    'guest', 'dbo' when the On condition is a_name = b_name?

    Because 'guest' != 'dbo'

    And why we get all the "wanted" rows with the WHERE condition b_name is null while I see 'dbo'?

    It confuses my brain!

    Gerhard P.Advanced BI DeveloperAustria

  • This is why I detest cursors for all but a few selective requirements.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • Jason-299789 (4/16/2015)


    This is why I detest cursors for all but a few selective requirements.

    1+

  • I don't want to use this statement. I want to understand it. Because learning never stops.

    Gerhard P.Advanced BI DeveloperAustria

  • Someone's apparently discovered an obscure bug that's being repeated here. Software bugs are just bugs, we can't "explain" them.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Ok, that's what I want to hear, except there is another reason.

    For me there are two possible answers

    First : this is something I don't understand

    Second: it's a bug.

    For me it's a bug. If anybody think this isn't, please let me know.

    Thank you all for replay, and for your patience.

    I'll close this thread.

    Gerhard P.Advanced BI DeveloperAustria

  • hudriwudri5 (4/16/2015)


    Ok, that's what I want to hear, except there is another reason.

    For me there are two possible answers

    First : this is something I don't understand

    Second: it's a bug.

    For me it's a bug. If anybody think this isn't, please let me know.

    Thank you all for replay, and for your patience.

    I'll close this thread.

    LOL...

    You can't close a thread, you mean you won't reply more. You're not an admin.

    And to be honest, I think investigating why the sets are different on this particular case, is futile. You should not use a cursor in the 1st place, not on this case.

  • The problem seems to be with the VALUES table value constructer

    Replace your foj to derived table with this ...

    full outer join

    (SELECT 'dbo' name) b on a.name = b.name

    .... and you get the expected output.

    So what do I think is happening? I think it is somehow reconstructing that table on each fetch, throwing the query evaluation out of line with information it has cached. When you do a static cursor, it gets the output into a temp storage hence why that fixes it). Although the output is more like it did a cross join

    I'd say that's a bug, but an easily avoidable one.

    EDIT: To make things weirder, if you also add ('Guest') to the values collection, it works again. And by the estimated plan the original query (with the dynamic keyword) actually tries to do a keyset instead of dynamic. And when I add the second value it does snapshot instead of dynamic, hence why it works suddenly.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply