SQLServer Optimizer Hints

Table Hints: Used to force index selection.
ex) SELECT memberID, fname, lname FROM members WITH (INDEX(memberID_index));

Join Hints: Used to specify the type of JOIN strategy used.
 - Loop, Merge, Hash가 있음. 

ex) FROM header_table INNER LOOP JOIN detail_table;
   OPTION (INNER) or OPTION (MERGE) or OPTION (HASH);

 

Query Hints: Hints used to affect GROUP BY and UNION functionality.

Lock Hints : Used to help avoid undesirable locking.
View Hints: Used to specify indexes in indexed views.

Other Hints: Misc. hints.

URL = http://www.sql-server-performance.com/tips/hints_general_p1.aspx

오라클에서는 Optimizer Hint를 자주 사용하는데 SQLServer는 접해볼 기회가 별루
없어서 사용해 본 적이 없네요.
기회가 되면 SQLServer에서도 Hint를 사용해서 옵티마이저를 조종해 보고 싶네요.