Control execution plans

May include but is not limited to: table hints, query hints

As you may know, SQL Server tries to use the best possible way to execute a query. It has very powerful query optimization techniques, with which you’ll be happy most of the time. But sometimes (very very rarely) you need to provide hints on how to execute a given statement (in a real world, you’d never do that, because it’s the responsibility of the DBA).

Fortunately, there are a bunch of built-in query hints. Even more fortunately, there isn’t a single goddamn word on these hints (to be funny, there isn’t any hint of them) in the Training Kit, so let’s query our old friend, MSDN.

Now there are two kind of these hints, query and table. Let’s start with the former. You can set a query hint on any query (the sole exception is when you use UNIONs), by adding the OPTION keyword, along with the desired hint to it. Here’s a brief list of what you may encounter:
Continue reading