KB4342424 - Improvement: Update to support QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n in USE HINT option in SQL Server 2017

Improvement

Assume that you have a SQL Server 2017 installed. This improvement introduces a new USE HINT 'QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n' to force the query optimizer behavior at a query level, as if the query was compiled with database compatibility level n, where n is a supported database compatibility level from the following list: 100, 110, 120, 130, or 140.

Note:

  • This hint does not override default or legacy cardinality estimation setting, if it is forced through database scoped configuration, trace flag or query hint.

    • If legacy cardinality estimation is forced through DB scoped config, trace flag or query hint, the query will continue to use legacy cardinality estimation as directed.

    • If default cardinality estimation is used for any reason (either forced, or because the query is run without an option to force legacy cardinality estimation), the query would use the cardinality estimator associated with the query optimizer for the database version requested.

  • 'ENABLE_QUERY_OPTIMIZER_HOTFIXES', trace flag (TF) 4199, and other methods of enabling optimizer hotfixes may override the default RTM behavior of the optimizer in some cases, regardless of the database compatibility level requested.

  • This hint only affects the behavior of the query optimizer. It does not affect other features of SQL Server that may depend on the database compatibility level, such as the availability of certain database features.

  • This hint does not require any special permissions and can be used with plan guides.

More Information

For more information, you can refer to the following links:

Resolution

This update is included in the following cumulative update for SQL Server:

       Cumulative Update 10 for SQL Server 2017

Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:

Latest cumulative update for SQL Server 2017

References

Learn about the terminologythat Microsoft uses to describe software updates.

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×