CODEWITHSUNDEEP
sql-server-2019
Arpit
Arpit

Reputation: 310

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?

Upvotes: 3

Views: 6438

Answers (2)

user2368632
user2368632

Reputation: 1073

SELECT * 
FROM FOO
OPTION(USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_150'))

QUERY_OPTIMIZER_COMPATIBILITY_LEVEL hint docs

Upvotes: 3

Robert Sievers
Robert Sievers

Reputation: 1355

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.

Upvotes: 2

Related Questions