Search code examples
sql-server-2019

Can Compatibility level be changed for a query in SQL Server 2019

In the past I have changed SQL Server compatibility level using an Alter Database statement. Is there a way to do it for an individual query rather than setting it for the complete database?

Solution

  • Starting with later versions of SQL, (I believe SQL 2017 and afterward), you can add this as a query hint.

    SELECT * FROM my_table QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_100

    for example, to run in 2008 mode. Or you could change the 100 to 150 to run in 2019 compatibility mode or anywhere in-between.