Query Optimization and Execution - Pro SQL Server Internals - page 474

One possible solution is to write the code that calculates the number of devices in the #ListOfDevices table and
compare it to the total number of devices per account, forcing SQL Server to use a specific index with an INDEX hint
based on the comparison results.
It is worth mentioning that such a system design is not optimal. It would be better to make DeviceId unique
system-wide rather than in the account scope. This would allow you to make DeviceId the leftmost column in the
nonclustered index, which can help SQL Server with cardinality estimations based on the list of devices.
This approach, however, would still not factor time parameters into such estimations.
FORCE ORDER Hint
A FORCE ORDER query hint preserves the join order in the query. When this hint is specified, SQL Server always joins
tables in the order in which joins are listed in the from clause of the query. However, SQL Server would choose the
least-expensive join type in each case.
Listing 25-22 shows an example of such a hint. SQL Server will perform joins in the following order:
((TableA join TableB) join TableC).
Listing 25-22. FORCE ORDER hint
select *
from
TableA join TableB on TableA.ID = TableB.AID
join TableC on TableB.ID = TableC.BID
option (force order)
LOOP, MERGE, and HASH JOIN Hints
You can specify join types with LOOP, MERGE, and HASH hints on both query and individual join levels. It is possible
to specify more than one join type in the query hint and allow SQL Server to choose the least-expensive one. A join
operator hint takes precedence over a query hint if both are specified. Finally, a join type hint also forces join orders
similarly to a FORCE ORDER hint.
Listing 25-23 shows an example of using join type hints. SQL Server will perform joins in the following order:
((TableA join TableB) join TableC). It will use a nested loop join to join TableA and TableB , and either a nested
loop or merge join for the TableC join.
Listing 25-23. Join type hints
select *
from
TableA inner loop join TableB on TableA.ID = TableB.AID
join TableC on TableB.ID = TableC.BID
option (loop join, merge join)
FORCESEEK/FORCESCAN Hints
A FORCESEEK hint prevents SQL Server from using Index Scan operators. It can be used on both query and individual
table levels. On the table level, it can also be combined with an INDEX hint.
Starting with SQL Server 2008R2 SP1, you can specify an optional list of columns for SEEK predicates. That type
of hint is not supported in SQL Server 2005, and it would generate an error if an execution plan without index scans
cannot be generated.
Starting with SQL Server 2008 R2 SP1, the opposite hint, FORCESCAN is available, which prevents SQL Server from
using Index Seek operators and forces it to scan data.