Compatibility level

-- get current compatibility level of DB

sp_dbcmptlevel 'AdventureWorks2008R2'

-- alter DB compatibility level

ALTER DATABASE AdventureWorks2008R2 SET COMPATIBILITY_LEVEL = 90

--or:

EXEC sp_dbcmptlevel AdventureWorks2008R2, 90

Changing the compatibility level while users are connected to the database can produce incorrect result sets for active queries. 

Therefore, the best practice to change the compatibility level of database is in following three steps:

ALTER DATABASE AdventureWorks2008R2 SET SINGLE_USER

GO

-- alter DB compatibility (2 options)

GO

ALTER DATABASE AdventureWorks2008R2 SET MULTI_USER

GO

Before upgrading Compatibility Level, it's good to check if the DB is ready to this upgrade, by one or more of those 2 ways:
1. run the Upgrade advisor
2. Script: testing all SPs before upgrading to Compatibility Level 90: 
http://www.sqlservercentral.com/scripts/Compatibility/62093/

Values of compability levels:

60 = SQL Server 6.0 

65 = SQL Server 6.5 

70 = SQL Server 7.0 

80 = SQL Server 2000 

90 = SQL Server 2005 

100= SQL Server 2008