A Tip On Query Hints

I try to avoid join and query hints in T-SQL.

Adding a hint to a query is like buying a tailored suit. As long as your body doesn’t change - the suit will look great, but a little variation in your weight can turn a great suit into a disaster. Likewise, a query hint might boost the performance of a query on today’s data – but what about next week’s (or the next client’s) data? A hint might handcuff and prevent the query optimizer from formulating a better query plan.

In my experience - join hints, index hints, and FORCE ORDER hints appear when SQL Server doesn’t understand the data as well as the person writing the query. To avoid hints, try to ‘describe’ the data to SQL Server using indexes and statistics. Also make sure to apply UNQIUE constraints on any field that will contain unique values. Proper database design will go a long way to avoiding the need for join hints.