Database Compatibility Level:

Whenever a database from one version of SQL Server is restored in newer version of SQL Server then do it started using all new features of newer version?

Answer is No. Certain database behavior does not change automatically when restored or attached in newer version of SQL Server. Database has a property named Compatibility Level that decides the version of SQL Server with which the database is to be made compatible. In SQL Server 2008, compatibility level of a database can be any one among 80, 90 or 100. Value 80 represent the SQL Server 2000, Value 90 represent 2005 and 100 represents to SQL Server 2008 behavior.

When a database is restored from previous version of SQL Server then it’s compatibility level is not automatically updated. It means database works with certain behavior of previous compatibility. This is a database specific property and does not affect other databases or server. The main purpose of this property is to provide backward compatibility.

In following diagram I used different color to represent different version of SQL Server. Smaller boxes inside the outer one’s represent databases and there color represent the environment according to there compatibility level.

 

Keeping a database in its previous compatibility has some benefits as well as few drawbacks.

Benefit: All the queries in that database are parsed and executed within previous version’s environment. So the changes of newer version like syntax change, new features or deprecated features do not cause the failure of running code. For example below statement is executed successfully in a SQL Server 2000 database (compatibility 80)

SELECT c1, c2 AS c1 FROM SampleTable ORDER BY c1;

But if we change the compatibility level of database to 100 then this statement will return error “Ambiguous column name 'c1'.”.

Drawback: Some enhancement and new features introduced in newer version can not be utilized.

How to change compatibility level of a database: Use ALTER DATABASE command as below:

ALTER DATABASE tempdb SET COMPATIBILITY_LEVEL = 80;

OR to make compatible with SQL Server 2008:

ALTER DATABASE tempdb SET COMPATIBILITY_LEVEL = 100;