select name, compatibility_level , version_name = CASE compatibility_level WHEN 65 THEN 'SQL Server 6.5' WHEN 70 THEN 'SQL Server 7.0' WHEN 80 THEN 'SQL Server 2000' WHEN 90 THEN 'SQL Server 2005' WHEN 100 THEN 'SQL Server 2008/R2' WHEN 110 THEN 'SQL Server 2012' WHEN 120 THEN 'SQL Server 2014' WHEN 130 THEN 'SQL Server 2016' WHEN 140 …

Aug 31, 2018 · The SQL Server version is the same regardless of database compatibility level. Database compatibility level allow one to upgrade to a newer SQL Server version yet preserve some legacy behavior at the database level until the application can be remediated. See the documentation for specifics on how the behaviors differ by compatibility levels.

Jan 6, 2020 · This should be helpfu and exactly what youre after. select name, compatibility_level , version_name = CASE compatibility_level WHEN 65 THEN 'SQL Server 6.5' WHEN 70 THEN 'SQL Server 7.0' WHEN 80 THEN 'SQL Server 2000' WHEN 90 THEN 'SQL Server 2005' WHEN 100 THEN 'SQL Server 2008/R2' WHEN 110 THEN 'SQL Server 2012' WHEN 120 THEN …

Feb 18, 2020 · 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.

Nov 28, 2012 · A quick information question : On my SQL Server 2008, I have databases migrated from SQL Server 2005 and the Compatibility level of the databases on SQL Server 2008 are set to 90 (SQL Server 2005).

Here you can read about the differences between compatibility level 80, 90 and 100. ALTER DATABASE Compatibility Level. Apparently new data types is not affected. I think that compatibility level is there to make SQL Server "behave" like the older version, not prevent you from doing new fancy stuff.

While a user does not need to be a server admin to change a database compatibility level, they do need to be a server admin in order for the drop-down to be enabled. Copy that into an answer and you'll get an accept.

Oct 3, 2013 · SQL Server Compatibility Level not working. 0. Database Compatibility Level. 2. SQL Server : database ...

Aug 8, 2017 · For SQL 2005 & newer: SELECT name, compatibility_level from sys.databases WHERE name = 'DatabaseNameHere' For SQL 2000:

However, beginning with SQL Server 2016, all the query optimizer improves are now based on compatibility level. If you are using SQL Server 2016 and your compatibility level is set to SQL Server 2016 (130), you can ignore this safely. You can set it on or off, it does not matter. SQL Server query optimizer hotfix trace flag 4199 servicing model