SQL SERVER 2008 JOIN hints

http://stackoverflow.com/questions/2446927/sql-server-2008-join-hints

Can someone please tell me why applying LOOP hints to all my queries is a bad idea. I read somewhere that a LOOP JOIN is default JOIN method for query optimiser but couldn't verify the validity of the statement?

Because this robs the optimizer of the opportunity to consider other methods which can be more efficient.

When are JOIN hints used? When the  hits the fan and ghost busters ain't in town?

When the data distribution (on which the optimizer makes its decisions) is severely skewed and the statistics are no able to represent it correctly.

What's the difference between LOOP, HASH and MERGE hints? BOL states that MERGE seems to be the slowest but what is the application of each hint?

These are different algorithms.

1. LOOP is nested loops: for each record from the outer table, the inner table is searched for matches (using the index of available). Fastest when only a tiny portion of records from both tables satisfy the JOIN and the WHERE conditions.

2. MERGE sorts both tables are traverses them in the sort order, skipping the unmatched records. Fastest for the FULL JOINs and when both recordsets are already sorted (from previous sort operations or when the index access path is used)

3. HASH build a hash table in the temporary storage (memory or tempdb) from one of the tables and searches it for each record from the other one. Fastest if the large portion of records from either table matches the WHERE and JOIN condition.