Compatibility Level in SQL Server

Compatibility Level:
It's used to govern the new features of sql server. It instructs the database to react accordinly. Consider am having a SQL Server 2000 database which is restored in SSMS(SQL Server Management Studio).
Right click the database properties and check the compatibility level in options tab, a drop down lists out different compatibility levels like,

Compatibility levels for different versions

70 = SQL Server 7.0 80 = SQL Server 2000 90 = SQL Server 2005 100 = SQL Server 2008

We can select among the options like, if we want legacy support for some queries or stored procedures we have to opt either Compatiblity level = 80 or 70.
Below is the query to retrieve the Compatibility Level of databases in a server,

SELECT name, cmptlevel FROM master.dbo.sysdatabases

It output resembles like,

Output

----------------------- Name Cmptlevel ----------------------- Master 90 MSDB 90 TempDB 90 Venkat 80 Model 90 ------------------------

T-SQL Query to update the Compatibility level,

EXEC sp_dbcmptlevel AdventureWorks, 80;

Regards,
Venkatesan Prabu .J