SQL Server 2019 Compatibility Level
With SQL Server 2019, there is a new compatibility level introduced, level 150 the new SQL Server 2019 Compatibility Level.
SQL Server can run in a number of different compatibility levels, but how do you change it and how do you set it. These compatibility levels reflect the version of SQL server.
- 60 = SQL Server 6.0
- 65 = SQL Server 6.5
- 70 = SQL Server 7.0
- 80 = SQL Server 2000
- 90 = SQL Server 2005
- 100 = SQL Server 2008 and SQL Server 2008 R2
- 110 = SQL Server 2012
- 120 = SQL Server 2014
- 130 = SQL Server 2016
- 140 = SQL Server 2017
- 150 = SQL Server 2019
SQL Server 2019 Supports compatibility level 100 and newer.
Changing the compatibility level in SQL Server may change the behavior of specific of the functionality of the SQL Server. Be sure to research and test before changing compatibility levels.
SQL Server 2019 Compatibility Level Sample Code
Here is a sample script that will show you the current compatibility level, set it to 150, then show you the current level after it has been set.
Sp_dbcmptlevel dbname GO Sp_dbcmptlevel dbname, 150 GO Sp_dbcmptlevel dbname GO
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
Hi Steve,
I have a question about compability mode. My application owner want to host there Database in SQL 2017. If I want to run host the database in SQL 2019 with compability level 140 because I have a existing SQL Hotel 2019, will this be exact the same as host it in a SQL 2017 SQL Server?
Thanks
Hakan
Compatibility level 140 is not exactly the same as running the server as version 2017. The compatibility level tells the SQL engine to run similar to version 2017, but other SQL 2019 features may still be different on that server.
I have found that if a vendor or application owner says you must run in a specific version of SQL Server it usually best to match that version and not try and fake it with compatibility levels.
Steve Stedman