This table lists the options that are available for join hints, query hints, and table hints in Microsoft® SQL Server™.
| Hint type | Option |
Description |
Default setting |
|---|---|---|---|
| Join | LOOP | HASH | MERGE | REMOTE |
Specifies the strategy to use when joining the rows of two tables. | Chosen by SQL Server |
| Query | { HASH | ORDER } GROUP | Specifies whether hashing or ordering is used to compute GROUP BY and COMUTE aggregations. | Chosen by SQL Server |
| Query | { MERGE | HASH | CONCAT } UNION |
Specifies the strategy to use for all UNION operations within the query. | Chosen by SQL Server |
| Query | FAST integer | Optimizes the query for retrieval of the specified number of rows. | No such optimization |
| Query | FORCE ORDER | Performs joins in the order in which the tables appear in the query | Chosen by SQL Server |
| Query | ROBUST PLAN | Creates a plan that accommodates maximum potential row size. | Chosen by SQL Server |
| Table | FASTFIRSTROW | Has the same effect as specifying the FAST 1 query hint. | No such optimization |
| Table | INDEX = | Instructs SQL Server to use the specified indexes for a table. | Chosen by SQL Server |
| Table | HOLDLOCK | SERIALIZABLE | REPEATABLEREAD | READCOMMITTED | READUNCOMMITTED | NOLOCK |
Causes certain locks to be set (or not) for a table, and overrides the locks that would be used to enforce the isolation level of the current transaction. | The locks required by the current transaction |
| Table | ROWLOCK | PAGLOCK | TABLOCK | TABLOCKX |
Specifies the size of the shared locks to be taken for this table. | Chosen by SQL Server |
| Table | READPAST | Skips locked rows altogether. | Wait for locked rows |
| Table | UPDLOCK | Takes update locks instead of shared locks. | Take shared locks |