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:
FROM sys.databases WHERE name = 'DatabaseName';
Change current Compatibility Level of a Database:
ALTER DATABASE DatabaseName
SET COMPATIBILITY_LEVEL = 110;
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:
ALTER DATABASE [DatabaseName] SET COMPATIBILITY_LEVEL = 110
ALTER DATABASE [DatabaseName] SET COMPATIBILITY_LEVEL = 100
ALTER DATABASE [DatabaseName] SET COMPATIBILITY_LEVEL = 90
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.