Does it matter what order you join tables in an SQL Server query? Not unless you use the command SET FORCEPLAN ON. If you use FORCEPLAN SQL Server will join the tables in the order you specify regardless of Query Optimizer calculations. If you do not specify that you wish to override the query optimizer, by setting FORCEPLAN on, which is the default configuration, then SQL Server uses index and table statistics to determine the best order of execution, which includes the order in which to join tables and the type of join to execute.
You still have a few different overrides available to you, even if you don’t force the entire join and execution plan. You can specify an index to be used when working with a table by using table hints. You can also specify the join type for each join operation.
These were things you might have used years ago. I have found that with each release of SQL Server it is getting increasingly difficult to beat the performance determined by the Query Optimizer, especially when your indexes and statistics are up to date. Moreover, when you override the Optimizer you are making an assumption that the best plan today will be the best plan in the future. The Query Optimizer does not make that assumption, adjusting to future data characteristics.
Do you find these query hints and user override capabilities to be useful in your environments? Why not leave a comment sharing how you have found them useful? Or drop an Email to btaylor@sswug.org.
Cheers,
Ben