what is the use of select hint in sql server? why they use? can you give example

Quote:

What is a Hint?
Hints are options and strong suggestions specified for enforcement by the SQL Server query processor on DML statements. The hints override any execution plan the query optimizer might select for a query.

Before we continue to explore this subject, we need to consider one very important fact and say some words of caution. 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 (or in development as a way to experiment and learn).

There are three different kinds of hints. Let us understand the basics of each of them separately.

Join Hint
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. Joins can be used in SELECT, UPDATE and DELETE statements.

Query Hint
This hint is used when certain kind of logic has to be applied to a whole 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. A query always has any of the following statements: SELECT, UPDATE, DELETE, INSERT or MERGE (SQL 2K8); and this hint can be applied to all of them.

Table Hint
This hint is used when certain kind of locking mechanism of tables has to be controlled. SQL Server query optimizer always puts the appropriate kind of lock on tables, when any of the Transact SQL operations SELECT, UPDATE, DELETE, INSERT or MERGE are used. There are certain cases when the developer knows when and where to override the default behavior of the locking algorithm and these hints are useful in those scenarios.

Let us run the following simple query with different kinds of query hints and observe the actual execution plan. The analysis of execution plan is not part of this article and will be covered in future.

USE AdventureWorks
GO
/* No Query Hint */
SELECT *
FROM Production.Product AS p
INNER JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = sod.ProductID
WHERE Weight = 20.77
GO
/* Merge Join Query Hint */
SELECT *
FROM Production.Product AS p
INNER MERGE JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = sod.ProductID
WHERE Weight = 20.77
GO
/* Hash Join Query Hint */
SELECT *
FROM Production.Product AS p
INNER HASH JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = sod.ProductID
WHERE Weight = 20.77
GO
/* Loop Join Query Hint */
SELECT *
FROM Production.Product AS p
INNER LOOP JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = sod.ProductID
WHERE Weight = 20.77
GO
/* Remote Join Query Hint */
SELECT *
FROM Production.Product AS p
INNER REMOTE JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = sod.ProductID
WHERE Weight = 20.77
GO
The above query will produce the following query execution plan.