Hints in SQL Server

Hints are the option, specified for enforcement by the SQL Server query processor on SELECT, INSERT, UPDATE or DELETE statements.

Note: - SQL Server Query optimizer is a very smart tool and it makes a best selection of execution plan. Suggesting hints to the Query Optimizer should be attempted when absolutely necessary and by experienced developers who know exactly what they are doing

Type of Hints: -

• Join Hints: - This hint is used when more than one table is used in a query. Two or more tables can be joined using different kinds of joins. This hint forces the type of join algorithm that is used between two tables. Join hints are specified in the FROM clause of a query. Joins can be used in SELECT, UPDATE and DELETE statements.
• Query Hints: - Query hints specify that the indicated hints should be used throughout the query. Any hint used in the query is applied to the complete query, as opposed to part of it. There is no way to specify that only a certain part of a query should be used with the hint. After any query, the OPTION clause is specified to apply the logic to this query.
  If one or more query hints cause the query optimizer not to generate a valid plan, error 8622 is raised.
  Query Hints applies to INSERT, UPDATE, DELETE, SELECT, MERGE statements.
• Table Hints: - This hint is used when certain kind of locking mechanism of tables has to be controlled. Table hints are specified in the FROM clause of the DML statement and affect only the table or view referenced in that clause. It applies to INSERT, UPDATE, DELETE, SELECT and MERGE.