Hello friends, let’s have a little idea about database compatibility levels and impact. I’ve phrased few common questions and answered those. Let’s have a look into those and feel free to ask in case of any confusion.
What is Database Compatibility Level?
Database compatibility level is just a number which indicates what all features your database still supports. This is used for backward compatibility. The compatibility level of a database dictates how certain language elements of the database function as it relates to an earlier version of SQL Server. In a nutshell, this offers up partial “backward compatibility” to an earlier version.
If you are having a database which is designed in Sql server 2000. In this case, compatibility level of your database will be 80.
Now your organization want to upgrade to Sql server 2005. You upgraded Sql server version and restored your old database to this newer version.
Perhaps you didn’t notice that there are few features which are there in Sql server 2000. You are using those features and now Sql server 2005 is not having those features. You may expect that those DB object will not work which are using old features. But you are wrong. Those features still work in newer version of Sql server. But how?
This is because of compatibility level. When you restored your database to newer Sql version then compatibility level of your database is still 80. This indicates to Sql server 2005 that there are still few features of Sql server 2000 which you may be using so those should not break.
So things will not break immediately. You have some time to find out these old features and replace those with new ones.
You can check compatibility level of your database from management studio.
Right click on your database and click on properties. Go to options tab and you’ll be able to see Compatibility Level.
In addition to this, you can check compatibility level of all databases using below query.
SELECT name, compatibility_level FROM sys.databases;
Who sets Compatibility Level?
Database compatibility level is initially set by Sql server. Following are few scenarios to understand this.
- You are creating a new database in Sql server 2012, then compatibility level of this database will be 110.
- If you are having a database which is created in Sql 2000 and now you are restoring this backup into Sql 2005 then compatibility level of this database will be 80.
- If you are having a database which is created in Sql 2000 and you created scripts for all DB objects. You created a new database in Sql server 2005 and run all scripts to make it replica of your old database. Then this new database will have compatibility level 90. Note: In this case your old features will not work as your database is having compatibility level according to Sql 2005.
Below is the chart of default compatibility levels of Sql server versions.
What is the Impact of this?
Let’s see this with an example. You have a database which is designed in Sql server 2000. So the compatibility of this database would be 80. Now suppose that your database is having few queries, stored procedures which are having non-ANSI joins.
select a.name,a.empno,b.loc from tab a, tab b where a.deptno *= b.deptno;
From above query you can see that we are using *= in query. This is kind of Left Outer Join which is supported in Sql version 2000 and lower.
Now you upgraded your Sql server to 2005 and restored your database. When you run your stored procedure which is having non-ANSI join, it’ll work fine because of compatibility level. Your database compatibility level is still 80.
If you change your database compatibility level to 90 then you may break things. So make sure that you replace all old features with new ones before upgrading compatibility level of your database.
Can I change it?
Of course, you can change it but now you understand impact of this. If you are using old features and changing compatibility level to new then you can break few stored procedure or other Db objects. So before changing compatibility level, make sure you have replaced all old features with new one.
Here is an example of how to set the database compatibility level in T-SQL:
ALTER DATABASE AdventureWorks2014 SET COMPATIBILITY_LEVEL = 120;
You can change it using management studio also. Just select compatibility level according to your case.
With SQL Server 2014 and newer, the database compatibility level also controls whether the new cardinality estimator or the legacy cardinality estimator is used. The new cardinality estimator gives equivalent or better query performance for many to most queries, but it sometimes has very significant performance regressions. It is very important to do as much testing as possible before you change the compatibility level to 120 or higher.
- Don’t assume that this setting will let you restore the database backward to an older version. You cannot restore new database backup to older version. There are certain rule for downgrading which you can check here. Sql Server Downgrades-Rules & Restrictions
- Don’t think that compatibility level is same as database version. Both are different properties with different concept behind. The database version is an internal versioning system that defines what version of SQL Server the database was a recent resident of. Will cover more details about database version in upcoming articles.
We have an option to help with upgrades i.e. Upgrade Advisor. SQL Server Upgrade Advisor helps you prepare for upgrades to SQL Server 2014. Upgrade Advisor analyzes installed components from earlier versions of SQL Server, and then generates a report that identifies issues to fix either before or after you upgrade.
You can get more information about upgrade advisor here Upgrade Advisor
Feels free to experiment new things and connect to me.