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.