How to Change/View Database Compatibility Levels in SQL Server 2008

Those of you used to working with SQL Server 2000 are familiar with using the stored procedure sp_dbcmptlevel to view and change the compatibility of a database. That stored proc has been deprecated however and it's replacements are far better!

By the way, LearnItFirst has some great SQL Server training classes that cover this same material:

• Database compatibility levels in SQL Server 2005
• Database compatibility levels in SQL Server 2008
  

To view a database's compatibility level in SQL Server 2008:

-- Old way:

EXEC sp_dbcmptlevel 'AdventureWorks'

-- New way:

SELECT name, compatibility_level from sys.databases WHERE name='AdventureWorks'

A super-awesome-party-time benefit of the new way is that, if you remove the WHERE clause, you can see the compatibility levels of all databases on the server! You can even filter to find only databases WHERE compatibility_level < 100

To change a database's compatibility level in SQL Server 2008:

-- Old way:

EXEC sp_dbcmptlevel 'AdventureWorks', 90

-- New way:

ALTER DATABASE 'AdventureWorks'

    SET COMPATIBILITY_LEVEL = 90 -- valid values are 80, 90, 100

Have fun!