SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 What is SQL Server Join Hints ?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jooorj
Posting Yak Master

126 Posts

Posted - 04/25/2011 :  19:32:28  Show Profile  Visit jooorj's Homepage  Reply with Quote
JOIN hints can be used in a query to specify the type of JOIN the Query Optimizer is to use for the execution plan. The JOIN options are:
•Loop •Merge •Hash
what is that means ?
What is LEFT OUTER JOIN HASH ?
sample

SELECT p.Name, pr.ProductReviewID
FROM Production.Product p
LEFT OUTER HASH JOIN Production.ProductReview pr
ON p.ProductID = pr.ProductID
ORDER BY ProductReviewID DESC;

Edited by - jooorj on 04/25/2011 19:43:12

robvolk
Most Valuable Yak

USA
15635 Posts

Posted - 04/25/2011 :  20:09:04  Show Profile  Visit robvolk's Homepage  Reply with Quote
JOIN hints are documented in Books Online, and are not recommended without extensive testing and comparison to non-hinted queries.
Go to Top of Page

jooorj
Posting Yak Master

126 Posts

Posted - 04/26/2011 :  01:29:54  Show Profile  Visit jooorj's Homepage  Reply with Quote
"JOIN hints are documented in Books Online" what do you mean ?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35934 Posts

Posted - 04/26/2011 :  01:32:03  Show Profile  Visit tkizer's Homepage  Reply with Quote
It means you can look up the information in SQL Server Books Online, which is the documentation that comes with the product.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

jooorj
Posting Yak Master

126 Posts

Posted - 04/26/2011 :  03:56:49  Show Profile  Visit jooorj's Homepage  Reply with Quote
Thank you I expected that you do not know the solution :)
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 04/26/2011 :  04:08:52  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
I'm quite sure that Tara knows full well what join hints are and what they do. The fact that we're referring you to documentation doesn't mean we don't know. Learning to use the documentation is essential for any DBA or database developer.

If, once you're read the documentation, you still have questions, please ask.

--
Gail Shaw
SQL Server MVP

Edited by - GilaMonster on 04/26/2011 04:28:20
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 04/26/2011 :  14:42:40  Show Profile  Reply with Quote
quote:
Originally posted by jooorj

Thank you I expected that you do not know the solution :)




That's one way to ingratiate your self

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

jooorj
Posting Yak Master

126 Posts

Posted - 04/26/2011 :  17:30:20  Show Profile  Visit jooorj's Homepage  Reply with Quote
please if you have answer tell me/?
Go to Top of Page

robvolk
Most Valuable Yak

USA
15635 Posts

Posted - 04/26/2011 :  17:32:43  Show Profile  Visit robvolk's Homepage  Reply with Quote
http://msdn.microsoft.com/en-us/library/ms173815.aspx
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 04/26/2011 :  18:33:40  Show Profile  Reply with Quote
Do you know how to spell ingratiate

http://www.imdb.com/title/tt0107048/quotes

Phil: I don't suppose there's any chance of a espresso or cappuccino?
Mrs. Lancaster: [confused look] Oh, I don't know...
Phil: [turns away, to self] ... how to /spell/ espresso or cappuccino.

PLEASE GO TO BOOKS ONLINE

Start>All Programs>Microsoft SQL Server 2008 R2 (hopefully)>Documentation and Tutorials>SQL Server Books Online

If Not Google Books Online

http://msdn.microsoft.com/en-us/library/ms130214.aspx

In any case, I personally don't recommend any Hints,except in RARE circumstances....and unless this is for a test (being that time of year)...then you should have studied

BOL:

quote:




The hash join has two inputs: the build input and probe input. The query optimizer assigns these roles so that the smaller of the two inputs is the build input.

Hash joins are used for many types of set-matching operations: inner join; left, right, and full outer join; left and right semi-join; intersection; union; and difference. Moreover, a variant of the hash join can do duplicate removal and grouping, such as SUM(salary) GROUP BY department. These modifications use only one input for both the build and probe roles.

The following sections describe different types of hash joins: in-memory hash join, grace hash join, and recursive hash join.

In-Memory Hash Join
The hash join first scans or computes the entire build input and then builds a hash table in memory. Each row is inserted into a hash bucket depending on the hash value computed for the hash key. If the entire build input is smaller than the available memory, all rows can be inserted into the hash table. This build phase is followed by the probe phase. The entire probe input is scanned or computed one row at a time, and for each probe row, the hash key's value is computed, the corresponding hash bucket is scanned, and the matches are produced.

Grace Hash Join
If the build input does not fit in memory, a hash join proceeds in several steps. This is known as a grace hash join. Each step has a build phase and probe phase. Initially, the entire build and probe inputs are consumed and partitioned (using a hash function on the hash keys) into multiple files. Using the hash function on the hash keys guarantees that any two joining records must be in the same pair of files. Therefore, the task of joining two large inputs has been reduced to multiple, but smaller, instances of the same tasks. The hash join is then applied to each pair of partitioned files.

Recursive Hash Join
If the build input is so large that inputs for a standard external merge would require multiple merge levels, multiple partitioning steps and multiple partitioning levels are required. If only some of the partitions are large, additional partitioning steps are used for only those specific partitions. In order to make all partitioning steps as fast as possible, large, asynchronous I/O operations are used so that a single thread can keep multiple disk drives busy.

Note:
If the build input is only slightly larger than the available memory, elements of in-memory hash join and grace hash join are combined in a single step, producing a hybrid hash join.



It is not always possible during optimization to determine which hash join is used. Therefore, SQL Server starts by using an in-memory hash join and gradually transitions to grace hash join, and recursive hash join, depending on the size of the build input.

If the optimizer anticipates wrongly which of the two inputs is smaller and, therefore, should have been the build input, the build and probe roles are reversed dynamically. The hash join makes sure that it uses the smaller overflow file as build input. This technique is called role reversal. Role reversal occurs inside the hash join after at least one spill to the disk.

Note:
Role reversal occurs independent of any query hints or structure. Role reversal does not display in your query plan; when it occurs, it is transparent to the user.



Hash Bailout
The term hash bailout is sometimes used to describe grace hash joins or recursive hash joins.

Note:
Recursive hash joins or hash bailouts cause reduced performance in your server. If you see many Hash Warning events in a trace, update statistics on the columns that are being joined.



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

Jahanzaib
Posting Yak Master

Pakistan
115 Posts

Posted - 04/27/2011 :  10:59:44  Show Profile  Visit Jahanzaib's Homepage  Reply with Quote
I think you are asking Query hints,There are 3 types of join option available for query optimizer to use it

1-MERGE
2-HASH
3-LOOP

define OPTION in the end of query

OPTION (MERGE JOIN)

Example

SELECT * FROM tab1 AS a
INNER JOIN tab2 AS b
ON a.cx= b.cx
WHERE cy = 5
OPTION (MERGE JOIN)


For further clarification visit on this link

http://aureus-salah.com/2011/02/25/sql-server-query-hints/

Regards,

Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

My Blog
www.aureus-salah.com

Edited by - Jahanzaib on 04/27/2011 11:00:18
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000