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.
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.
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.
sp_dbcmptlevel 'AdventureWorks',100
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
ALTER DATABASE AdventureWorks SET COMPATIBILITY_LEVEL = 100 GO
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.
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
