SQL Join Hints

Question

SQL Join Hints

IN_Sandeep

SSCertifiable

Points: 6259
More actions
May 5, 2008 at 1:32 am

#181897

Hi Friends,
I just come across the topic of join hints and I found its interesting if we can change the join type by giving the join hints.
Is it Possible?
I mean to say, If I have a INNER JOIN and its follows nested join hints, how can change this to hash join or merge join.
If yes, then how can I do it?
Cheers!
Sandy.
--

Viewing 8 posts - 1 through 7 (of 7 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

It is possible to force the type of join (nested loop, merge, hash) you want, however, unless you really understand how the different joins work, when the different types are optimal and you are sure that you know better than the optimiser, stay away from hints of any form.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 2

Thanks for Reply,

Can you just guide me how can it possible?

I mean the syntax for this,

it would be better.

Cheers!

Sandy.

--

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

... FROM tbl1 INNER LOOP JOIN tbl2 ON ... -- nested loop join

... FROM tbl1 INNER MERGE JOIN tbl2 ON ... -- merge join

... FROM tbl1 INNER HASH JOIN tbl2 ON ... -- hash join

Again, just for emphasis, rather don't use these.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 4

Great,

But Can I specify the type of hash join like (In-Memory Hash Join, Grace Hash Join, Recursive Hash Join) ?

Cheers!

Sandy.

--

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

No. those will be up to the query optimiser/query processor depending on memory availability, number of rows, etc

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 6

IN_Sandeep

SSCertifiable

Points: 6259

May 5, 2008 at 3:39 am

#810569

Ok,

Thanks for guiding me.

Cheers!

Sandy.

--

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 7

Listen to Gail. Don't use these.

However, if you do, test, test, test, the queries to verify that you're actually getting usefulness out of them.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning