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.

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from dbrnd.com

Leave a Reply

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

Notify of

Sort by:   newest | oldest | most voted
9 months 15 days ago

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