This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a TSQL script to find and change the compatibility level of SQL Server Database.
Compatibility level provides only partial backward compatibility with earlier versions of the SQL Server.
Sometimes, we need to change the compatibility level for the migration-related work.
You can also check and change Compatibility Level using the SSMS, but I always prefer the TSQL script.
Find the current Compatibility level of a Database:
|
1 2 |
SELECT compatibility_level FROM sys.databases WHERE name = 'DatabaseName'; |
Change current Compatibility Level of a Database:
|
1 2 3 |
ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 110; GO |
List of Compatibility Levels:
- 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:
|
1 2 3 |
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.
