SQL Server: Script to find and change Compatibility Level of Database

In this post, I am sharing one TSQL script to find and change the compatibility level of SQL Server Database.

Compatibility level provides only partial backward compatibility with earlier versions of SQL Server.
Sometimes we require to change the compatibility level during migration related work.

You can also check and change Compatibility Level using SSMS but I always prefer to use TSQL script.

Find the current Compatibility level of a Database:

Change current Compatibility Level of a Database:

List of Compatibility Level:

  • SQL Server 2016: 130
  • SQL Server 2014: 120
  • SQL Server 2012: 110
  • SQL Server 2008: 100
  • SQL Server 2005: 90
  • SQL Server 2000: 80

Example of valid script to change the compatibility level of SQL Server 2012:

You cannot change it to COMPATIBILITY_LEVEL 80 in SQL SERVER 2012.

Anvesh Patel

Leave a Reply

2 Comments on "SQL Server: Script to find and change Compatibility Level of Database"

Notify of
avatar
Sort by:   newest | oldest | most voted
chasity
Guest

Hi, I want to subscribe foor thjs weblog to obtain latest updates, therefore where can i do it please help out.

wpDiscuz