April 15, 2015 at 6:08 am
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
April 15, 2015 at 7:31 am
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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 15, 2015 at 7:37 am
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).
April 15, 2015 at 7:55 am
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
April 15, 2015 at 11:28 am
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
April 15, 2015 at 12:36 pm
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.
April 16, 2015 at 12:44 am
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
April 16, 2015 at 4:56 am
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
April 16, 2015 at 5:47 am
This is why I detest cursors for all but a few selective requirements.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
April 16, 2015 at 8:32 am
Jason-299789 (4/16/2015)
This is why I detest cursors for all but a few selective requirements.
1+
April 16, 2015 at 9:39 am
I don't want to use this statement. I want to understand it. Because learning never stops.
Gerhard P.Advanced BI DeveloperAustria
April 16, 2015 at 10:05 am
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 16, 2015 at 10:47 am
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
April 16, 2015 at 10:52 am
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.
April 16, 2015 at 1:58 pm
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