The following table highlights SQL hints based on MS SQL Server hints optimization.
| Category | Hint | Available For | Notes |
|---|---|---|---|
| QUERY | IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX | SELECT/INSERT/UPDATE/DELETE/MERGE | This hint lets you disable use of a nonclustered xVelocity memory optimized columnstore index. |
| JOIN | LOOP | SELECT/UPDATE/DELETE | Not applicable for RIGHT OUTER or FULL joins. |
| JOIN | HASH | SELECT/UPDATE/DELETE | -- |
| JOIN | MERGE | SELECT/UPDATE/DELETE | -- |
| JOIN | REMOTE | SELECT/UPDATE/DELETE | Only for INNER JOINs. Not applicable with COLLATE. |
| QUERY | RECOMPILE | SELECT/UPDATE/DELETE | -- |
| QUERY | FORCE ORDER | SELECT/UPDATE/DELETE | -- |
| QUERY | ROBUST PLAN | SELECT/UPDATE/DELETE | -- |
| QUERY | KEEP PLAN | SELECT/UPDATE/DELETE | -- |
| QUERY | KEEPFIXED PLAN | SELECT/UPDATE/DELETE | -- |
| QUERY | EXPAND VIEWS | DML Statements | Only for statement containing views. |
| QUERY | HASH GROUP | SELECT | Only when GROUP BY, COMPUTE and DISTINCT clauses are used. |
| QUERY | ORDER GROUP | SELECT/UPGRADE/DELETE | Only when GROUP BY, COMPUTE and DISTINCT clauses are used. |
| QUERY | MERGE UNION | SELECT | Only for statements chained using UNION. |
| QUERY | HASH UNION | SELECT | Only for statements chained using UNION. |
| QUERY | CONCAT UNION | SELECT | Only for statements chained using UNION. |
| QUERY | LOOP JOIN | SELECT/UPGRADE/DELETE | -- |
| QUERY | MERGE JOIN | SELECT/UPGRADE/DELETE | -- |
| QUERY | HASH JOIN | SELECT/UPGRADE/DELETE | -- |
| Table | FORCESCAN | SELECT/UPGRADE/COMPLETE | Forces the optimizer to use an index scan operation as the access path to the referenced table or view. |
| Table | FORCESEEK | SELET/UPDATE/COMPLETE | Forces the optimizer to use an index seek operation as the access path to the referenced table or view. |
| Table | INDEX() | DML Statements | Only for tables and views with indexes. |
| Table | KEEPIDENTITY | INSERT | Only for INSERT statements using OPENROWSET clause with BULK option. |
| Table | KEEPDEFAULTS | INSERT | Only for INSERT statements using OPENROWSET clause with BULK option. |
| Table | HOLDLOCK | DML Statements | Not applicable for SELECT statements using FOR BROWSE clause. |
| Table | IGNORE_CONSTRAINTS | INSERT | Only for INSERT statements using OPENROWSET clause with BULK option. |
| Table | IGNORE_TRIGGERS | INSERT | Only for INSERT statements using OPENROWSET clause with BULK option. |
| Table | NOLOCK | SELECT/UPDATE/COMPLETE | Not applicable for the target table in UPDATE/DELETE statements. |
| Table | NOWAIT | DML Statements | -- |
| Table | PAGELOCK | DML Statements | -- |
| Table | READCOMMITTED | DML Statements | -- |
| Table | READCOMMITTEDLOCK | SELECT/UPDATE/COMPLETE | -- |
| Table | READPAST | SELECT/UPDATE/COMPLETE | Not applicable for the target table in UPDATE/DELETE statements. |
| Table | READUNCOMMITTED | SELECT/UPDATE/COMPLETE | Not applicable for the target table in UPDATE/DELETE statements. |
| Table | REPEATABLEREAD | DML Statements | -- |
| Table | ROWLOCK | DML Statements | -- |
| Table | SERIALIZABLE | DML Statements | Not applicable for SELECT statements using FOR BROWSE clause. |
| Table | SPATIAL_WINDOW_MAX_CELLS | DML Statements | Specifies the maximum number (1 - 8192) of cells to use when tessellating a geometry or geography object. |
| Table | TABLOCK | DML Statements | -- |
| Table | TABLOCKX | DML Statements | -- |
| Table | UPDLOCK | DML Statements | -- |
| Table | XLOCK | DML Statements | -- |