Many a times one may need to change compatibility level of a database. In this blog we’ll see how to change database compatibility level or mode of a SQL Server database.  To get the current database compatibility level execute the query shown in below snapshot.

3_How to change database compatibility level in SQL Server

A database compatibility level can be changed by using any of the below mentioned methods.

Method 1: Using sp_dbcmptlevel

To check available compatibility level for a database, execute the command as shown in below snapshot.

1_How to change database compatibility level in SQL Server

As shown in above snapshot, a database can be changed to compatible levels of 100/110/120. This is on SQL Server 2014.

Change the compatible by executing the below query.

The procedure sp_dbcmptlevel will be removed in future versions of SQL Server.

Method 2: Alter database

A database compatibility level can be changed by Alter database command as shown below

Method 3: GUI

Connect to object explorer, right click the database you want to change the compatibility mode and select properties. This will bring up the database properties dialog box. On the left hand side select Options and then change the compatibility level in compatibility level combo box as shown in below snapshot.

2_How to change database compatibility level in SQL Server

 

Regards

Ahmad Osama

Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook