Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

left hash JOIN - Could it ever give wrong results? Expand / Collapse
Author
Message
Posted Friday, March 22, 2013 12:46 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 10:33 AM
Points: 1,283, Visits: 2,959

Let me tell you, i do know using hints should be the last option. I am really in a situation where i need to address a issue by hook or crook ( actually most of my issues are addressed like this ). I have a left join which is executing for each record, i looked in the exec plan and it is using a Nested loop. My goal was to have it execute ONLY once, so i replaced with left hash JOIN and this made it execute only once and made query much much faster. My question is would this type left hash JOIN ever NOT GIVE the same result as LEFT Join.
Post #1434479
Posted Friday, March 22, 2013 3:29 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:45 AM
Points: 185, Visits: 924
sqldba_newbie (3/22/2013)

Let me tell you, i do know using hints should be the last option. I am really in a situation where i need to address a issue by hook or crook ( actually most of my issues are addressed like this ). I have a left join which is executing for each record, i looked in the exec plan and it is using a Nested loop. My goal was to have it execute ONLY once, so i replaced with left hash JOIN and this made it execute only once and made query much much faster. My question is would this type left hash JOIN ever NOT GIVE the same result as LEFT Join.

Only if there is a bug in SQL Server, because query hints should not affect the results at all.

And also consider this:
http://msdn.microsoft.com/en-us/library/ms181714.aspx
If one or more query hints cause the query optimizer not to generate a valid plan, error 8622 is raised.



Alex Suprun
Post #1434529
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse