x

Questions about JOIN

Please help me with the following questions as soon as possible….

1) True or False, specifying a join hint will force join order?

2) When joining two small row sets together on an inner join, which join type is generally preferred?

a. Hash Join

b. Merge Join

c. Loop Join

3) What is the difference between a hash join and a merge join?

Thank you.

more ▼

asked Dec 05 '09 at 01:44 AM in Default

sqlnewbiee gravatar image

sqlnewbiee
1 1 1 1

Why is this stuff urgent?
Dec 05 '09 at 12:10 PM Grant Fritchey ♦♦
Because planning to do homework in advance is a hassle?
Dec 05 '09 at 12:28 PM Matt Whitfield ♦♦
Or because they needed to fill out interview questions: http://www.sqlservercentral.com/Forums/Topic829341-1291-1.aspx
Dec 07 '09 at 11:09 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

1) Join order will only be forced if you use the query hint that forces the order, FORCE ORDER. Then it will follow the order of the joins in the query.

2) There is always "it depends" to apply but generally the answer to this would be a loop join

3) First, a merge join requires that both inputs be sorted, so you'll frequently see extra sort operations ahead of a merge join when it's dealing with un-ordered data. A merge will take a row from each input and compare them, one a time. The merge is preferred on very large data sets where the data is pre-sorted. A hash join is usually between two sets of data where one is smaller than the other, similar to the loop. In this case the smaller input is built as a hash table, either in memory, or out to disk, with the key values being created as a hash. Then the other input is probed, a row at a time, comparing it's key, after hashing, to the hash table. There's more to it than that, but that's the basics. Usually a hash join works better on larger data sets, but where one is clearly smaller than the other. As with #2 above, "it depends" plays pretty heavily into which one works better in which situation.

more ▼

answered Dec 05 '09 at 12:09 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
91k 19 21 74

I have to add that anyone who hadn't read Grant's excellent book 'SQL Server Execution Plans' would struggle with these interview questions. Be prepared! Buy his book!
Dec 18 '09 at 09:04 AM Phil Factor
Thanks Phil. Appreciate the plug.
Dec 18 '09 at 01:05 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

Grant has you right on the money.

If are hand tuning a lot of your queries you likely have other design issues. The execution plans are based on statistics and as the size of the subset change the plan might need to change also.

Not only the overall design but look at the design of the querry itself. I have sped up a lot of queries by moving condidtions to the JOIN and taking them out of the where.

more ▼

answered Dec 05 '09 at 12:26 PM

Charles Kincaid gravatar image

Charles Kincaid
11

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x977
x111
x47
x23
x5

asked: Dec 05 '09 at 01:44 AM

Seen: 2415 times

Last Updated: Dec 05 '09 at 07:27 PM