SQL Server Compatibility Level

SQL Server Compatibility level will be used to decide how SQL will use it features. By setting compatibility level will make sure that SQL will follow that particular compatibility level version. It is a database property and will only affects the features for that particular database. For example with SQL 2014 or higher the new cardinality estimator is used however  if you are on SQL 2012 or low version then the old cardinality estimator is used .

Database compatibility will assist database upgrade by allowing the SQL server database engine to be upgraded and keeping the same pre-upgrade database compatibility level. The database compatibility can be change when you want to use the new features for the higher compatibility level.

Backward compatibility :

Database compatibility will be always backward with earlier version of SQL server.

Example :

SQL Server 2017 will be backward compatible for the below SQL Server versions:

• SQL Server 2017 (140)
• SQL Server 2016 (130)
• SQL Server 2014 (120)
• SQL Server 2012 (110)
• SQL Server 2008/R2 (100)

However back ward compatible is not possible when you try to restore it to an older instance version.

Let’s consider that you have a database on SQL Server 2017 and with compatibility level SQL server 2014(120),if you try to restore the backup of this database on SQL Server 2014 instance you cannot restore it. On the other side if you are on SQL server 2014 and with compatibility level SQL server 2014(120) you can restore this backup to SQL server 2016.

Below is the compatibility chart by instance :

For latest version of the compatibility by instance check this MSDN link.

In this post you can find out on  How to Check Compatibility level of database.

In this post you can find out on How to Alter Compatibility level of database.

 2,441 total views,  1 views today