Today we have a very interesting subject to look at. I tried to look for help online but have not found any other documentation besides what we have from the Book Online.
Let us try to understand what are the different kinds of hints available in SQL Server and how they are helpful.
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.
Reference : Pinal Dave (http://blog.sqlauthority.com)