This query hint virtually disallows direct use of indexed views and indexes on indexed views in the query plan.

Changes the [recompilation thresholds](../../relational-databases/statistics/statistics.md#auto_update_statistics-option) for temporary tables, and makes them identical to those for permanent tables. The estimated recompile threshold starts an automatic recompile for the query when the estimated number of indexed column changes have been made to a table by running one of the following statements:

- MERGE

- INSERT

**Applies to**: [!INCLUDE [ssNoVersion](../../includes/ssnoversion-md.md)] (starting with [!INCLUDE [ssSQL11](../../includes/sssql11-md.md)] Service Pack 3, [!INCLUDE [ssSQL14](../../includes/sssql14-md.md)] Service Pack 2 and [!INCLUDE [ssazure-sqldb](../../includes/ssazure-sqldb.md)].

**Applies to**: [!INCLUDE [ssNoVersion](../../includes/ssnoversion-md.md)] (starting with [!INCLUDE [ssSQL11](../../includes/sssql11-md.md)] Service Pack 3, [!INCLUDE [ssSQL14](../../includes/sssql14-md.md)] Service Pack 2 and [!INCLUDE [ssazure-sqldb](../../includes/ssazure-sqldb.md)].

The minimum memory grant size in PERCENT of configured memory limit. The query is guaranteed to get `MAX(required memory, min grant)` because at least required memory is needed to start a query. Valid values are between 0.0 and 100.0.

Overrides the **max degree of parallelism** configuration option of `sp_configure`. Also overrides the Resource Governor for the query specifying this option. The MAXDOP query hint can exceed the value configured with `sp_configure`. If MAXDOP exceeds the value configured with Resource Governor, the [!INCLUDE [ssDE](../../includes/ssde-md.md)] uses the Resource Governor MAXDOP value, described in [ALTER WORKLOAD GROUP (Transact-SQL)](../statements/alter-workload-group-transact-sql.md). All semantic rules used with the **max degree of parallelism** configuration option are applicable when you use the MAXDOP query hint. For more information, see [Configure the max degree of parallelism Server Configuration Option](../../database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option.md).

Prevents a spool operator from being added to query plans (except for the plans when spool is required to guarantee valid update semantics). The spool operator may reduce performance in some scenarios. For example, the spool uses `tempdb`, and `tempdb` contention can occur if there are many concurrent queries running with the spool operations.

Instructs the Query Optimizer to use the average selectivity of the predicate across all column values instead of using the runtime parameter value when the query is compiled and optimized.

This option lets you enable a plan-affecting trace flag only during single-query compilation. Like other query-level options, you can use it together with plan guides to match the text of a query being executed from any session, and automatically apply a plan-affecting trace flag when this query is being compiled. The QUERYTRACEON option is only supported for Query Optimizer trace flags. For more information, see [Trace Flags](../database-console-commands/dbcc-traceon-trace-flags-transact-sql.md).

To use more than one trace flag in a query, specify one QUERYTRACEON hint for each different trace flag number.

Provides one or more additional hints to the query processor. The additional hints are specified by a hint name **inside single quotation marks**.

Causes [!INCLUDE [ssNoVersion](../../includes/ssnoversion-md.md)] to generate a plan using maximum selectivity when estimating AND predicates for filters to account for full independence. This hint name is the default behavior of the cardinality estimation model of [!INCLUDE [ssSQL11](../../includes/sssql11-md.md)] and earlier versions, and equivalent to [trace flag](../database-console-commands/dbcc-traceon-trace-flags-transact-sql.md) 9472 when used with cardinality estimation model of [!INCLUDE [ssSQL14](../../includes/sssql14-md.md)] or higher.

- 'ASSUME_PARTIAL_CORRELATION_FOR_FILTER_ESTIMATES'

Causes [!INCLUDE [ssNoVersion](../../includes/ssnoversion-md.md)] to generate a plan using most to least selectivity when estimating AND predicates for filters to account for partial correlation. This hint name is the default behavior of the cardinality estimation model of [!INCLUDE [ssSQL14](../../includes/sssql14-md.md)] or higher.

- 'DISABLE_BATCH_MODE_ADAPTIVE_JOINS'

Disables batch mode adaptive joins. For more information, see [Batch mode Adaptive Joins](../../relational-databases/performance/intelligent-query-processing-details.md#batch-mode-adaptive-joins).

- 'DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK'

Disables batch mode memory grant feedback. For more information, see [Batch mode memory grant feedback](../../relational-databases/performance/intelligent-query-processing-feedback.md#batch-mode-memory-grant-feedback).

- 'DISABLE_DEFERRED_COMPILATION_TV'

Disables table variable deferred compilation. For more information, see [Table variable deferred compilation](../../relational-databases/performance/intelligent-query-processing-details.md#table-variable-deferred-compilation).

- 'DISABLE_INTERLEAVED_EXECUTION_TVF'

Disables interleaved execution for multi-statement table-valued functions. For more information, see [Interleaved execution for multi-statement table-valued functions](../../relational-databases/performance/intelligent-query-processing-details.md#interleaved-execution-for-mstvfs).

- 'DISABLE_OPTIMIZED_NESTED_LOOP'

Causes SQL Server to generate a plan that doesn't use row goal modifications with queries that contain these keywords:

- IN

This hint name is equivalent to [trace flag](../database-console-commands/dbcc-traceon-trace-flags-transact-sql.md) 4138.

Disables row mode memory grant feedback. For more information, see [Row mode memory grant feedback](../../relational-databases/performance/intelligent-query-processing-feedback.md#row-mode-memory-grant-feedback).

- 'DISABLE_TSQL_SCALAR_UDF_INLINING'

Disables scalar UDF inlining. For more information, see [Scalar UDF Inlining](../../relational-databases/user-defined-functions/scalar-udf-inlining.md).

- 'DISALLOW_BATCH_MODE'

Disables batch mode execution. For more information, see [Execution modes](../../relational-databases/query-processing-architecture-guide.md#execution-modes).

- 'ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS'

Forces the Query Optimizer behavior at a query level. This behavior happens as if the query was compiled with database compatibility level *n*, where *n* is a supported database compatibility level (for example 100, 130, etc.). Refer to [sys.dm_exec_valid_use_hints](../../relational-databases/system-dynamic-management-views/sys-dm-exec-valid-use-hints-transact-sql.md) for a list of currently supported values for *n*.

> [!NOTE]

> The QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n hint doesn't override default or legacy cardinality estimation setting, if it's forced through database scoped configuration, trace flag or another query hint such as QUERYTRACEON.

When you specify *exposed_object_name* without also specifying a table hint, any indexes you specify in the query as part of a table hint for the object are disregarded. The Query Optimizer then determines index usage. You can use this technique to eliminate the effect of an INDEX table hint when you can't modify the original query. See Example J.

NOEXPAND [ , INDEX ( *<index_value>* [ ,...n ] ) | INDEX = ( *<index_value>* ) ] | INDEX ( *<index_value>* [ ,...n ] ) | INDEX = ( *<index_value>* ) | FORCESEEK [(*<index_value>*(*<index_column_name>* [,... ] ) ) ] | FORCESCAN | HOLDLOCK | NOLOCK | NOWAIT | PAGLOCK | READCOMMITTED | READCOMMITTEDLOCK | READPAST | READUNCOMMITTED | REPEATABLEREAD | ROWLOCK | SERIALIZABLE | SNAPSHOT | SPATIAL_WINDOW_MAX_CELLS = *<integer_value>* | TABLOCK | TABLOCKX | UPDLOCK | XLOCK }

When specified as a query hint, the INDEX, FORCESCAN, and FORCESEEK table hints are valid for the following objects:

- Indexed views

- Common table expressions (the hint must be specified in the SELECT statement whose result set populates the common table expression)

- Dynamic Management Views (DMVs)