Friday, September 2, 2016

Check the SQL Server Database Compatibility Level

Just a quick script to check your database compatibility level... and change it, if you need to.  This statement will bring back the compatibility levels for each database in the system:

     /* query the db compatibility level */
     SELECT
          name [database],
          compatibility_level ,
          [version] =
          CASE compatibility_level
             WHEN 65  THEN 'SQL Server 6.5'
             WHEN 70  THEN 'SQL Server 7.0'
             WHEN 80  THEN 'SQL Server 2000'
             WHEN 90  THEN 'SQL Server 2005'
             WHEN 100 THEN 'SQL Server 2008/R2'
             WHEN 110 THEN 'SQL Server 2012'
             WHEN 120 THEN 'SQL Server 2014'
             WHEN 130 THEN 'SQL Server 2016' END
     FROM
          sys.databases;
     
And when you need to change it:

     /* change db compatibility level */
     ALTER DATABASE YourDatbaseName
     SET COMPATIBILITY_LEVEL = 110;

Be wary, though, about changing the compatibility level.  Often times by doing so, you will be unable to take advantage of certain features. Take a look at this MSDN reference for more details on the differences between higher/lower compatibility levels: