how to use join hints ?

15 Jul 2005 4:14 AM

Hassan

Can someone send me a query example on how to force join hints ? Id like to
try different options such as nested, merge,hash

Thanks

15 Jul 2005 4:32 AM

David Gugick

Hassan wrote:

> Can someone send me a query example on how to force join hints ? Id
> like to try different options such as nested, merge,hash
>
> Thanks

-- hash join
select *
from sysobjects a inner hash join syscolumns b
on a.id = b.id

-- merge join
select *
from sysobjects a inner merge join syscolumns b
on a.id = b.id

-- nested loop join
select *
from sysobjects a inner loop join syscolumns b
on a.id = b.id

In almost all cases, you should leave this process of deciding to SQL
Server.

Or do you mean forcing indexes?

--
David Gugick
Quest Software
www.imceda.com
www.quest.com

15 Jul 2005 9:44 PM

Hassan

i know there is an option to force join hints.. and u can add this join hint
at the end
Show quote

"David Gugick" <david.gugick-nospam@quest.com> wrote in message
news:eGMT%23YPiFHA.3256@TK2MSFTNGP12.phx.gbl...
> Hassan wrote:
> > Can someone send me a query example on how to force join hints ? Id
> > like to try different options such as nested, merge,hash
> >
> > Thanks
>
> -- hash join
> select *
> from sysobjects a inner hash join syscolumns b
> on a.id = b.id
>
> -- merge join
> select *
> from sysobjects a inner merge join syscolumns b
> on a.id = b.id
>
> -- nested loop join
> select *
> from sysobjects a inner loop join syscolumns b
> on a.id = b.id
>
> In almost all cases, you should leave this process of deciding to SQL
> Server.
>
> Or do you mean forcing indexes?
>
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

15 Jul 2005 11:12 PM

David Gugick

Hassan wrote:

> i know there is an option to force join hints.. and u can add this
> join hint at the end

I'm not sure what you mean...

--
David Gugick
Quest Software
www.imceda.com
www.quest.com

16 Jul 2005 12:05 AM

--CELKO--

Just play with it and see what happens. Then never use it. It is a
"vendor kludge".

"Ain't nothing more permanent than a 'temporary fix' in programming" --
IT proverb

Once you put a HINT into production, it will never be removed and your
code will always be compiled that way. The next generation knows why
it was put into place, so they are afraid to remove it. Hey, the
current version is running and we can tolerate it's performance, so
why bother?