SQL SERVER 2012 :
Given below are the methods you can use to change the compatibility level in SQL Server 2012.
Method 1 :
ALTER DATABASE [AdventureWorks2012] SET COMPATIBILITY_LEVEL = 110 ALTER DATABASE [AdventureWorks2012] SET COMPATIBILITY_LEVEL = 100 ALTER DATABASE [AdventureWorks2012] SET COMPATIBILITY_LEVEL = 90
But you cannot change it to COMPATIBILITY LEVEL 80 in SQL SERVER 2012.
Lets try to change the compatibility level to 80 in SQL SERVER 2012.
ALTER DATABASE [AdventureWorks2012] SET COMPATIBILITY_LEVEL = 80 --RESULT
Msg 15048, Level 16, State 1, Line 1
Valid values of the database compatibility level are 90, 100, or 110.
Version of SQL Server with respect to compatibility level.
- SQL Server 2012 = COMPATIBILITY_LEVEL 110
- SQL Server 2008 = COMPATIBILITY_LEVEL 100
- SQL Server 2005 = COMPATIBILITY_LEVEL 90
- SQL Server 2000 = COMPATIBILITY_LEVEL 80
Method 2 :
EXEC sp_dbcmptlevel [AdventureWorks2012] , 110;
This method will not be supported from the future version of SQL SERVER. So, don’t use this in the future development.
SQL SERVER 2005 / 2008 :
Given below is the method to set the compatibility level in SQL SERVER 2005 and above.
EXEC sp_dbcmptlevel [AdventureWorks] , 90;
SQL Server Portal 