Sign in with Microsoft
Sign in or create an account.
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.


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.


  • 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:


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


Learn about the terminologythat Microsoft uses to describe software updates.

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!