compatibility level change
 
CREATE DATABASE statement and then issues “sp_dbcmptlevel” to set the database compatibility level to 80 as shown below.

CREATE DATABASE [test] ON PRIMARY ( NAME = N’test’, FILENAME = N’Z:\SQLData\test.mdf’ , SIZE = 2048KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N’test_log’, FILENAME = N’Y:\SQLData\test3_log.ldf’ , SIZE = 3072KB , FILEGROWTH = 10%) GO EXEC dbo.sp_dbcmptlevel @dbname=N’test’, @new_cmptlevel=80 GO

---

Changing Compatibility Level
So once you have identified the compatibility level of your database and know what you want to change it to you can use the sp_dbcmptlevel system stored procedure to make the change.  The command has the following syntax:

sp_dbcmptlevel [ [ @dbname = ] name ] [ , [ @new_cmptlevel = ] version ]–to change to level 80 dbo.sp_dbcmptlevel @dbname=N’test’, @new_cmptlevel=80 –to change to level 90 dbo.sp_dbcmptlevel @dbname=N’test’, @new_cmptlevel=90 –or sp_dbcmptlevel ‘test’, ’80′ sp_dbcmptlevel ‘test’, ’90′

---

Differences
There are several differences on how compatibly levels affect your database operations.  SQL Server Books Online has a list of these differences and the following list shows you a few of these items:

Compatibility level setting of 80 or earlier	Compatibility level setting of 90	Possibility of impact
For locking hints in the FROM clause, the WITH keyword is always optional.	With some exceptions, table hints are supported in the FROM clause only when the hints are specified with the WITH keyword. For more information, see FROM (Transact-SQL).	High
The *= and =* operators for outer join are supported with a warning message.	These operators are not supported; the OUTER JOIN keyword should be used.	High
SET XACT_ABORT OFF is allowed inside a trigger.	SET XACT_ABORT OFF is not allowed inside a trigger.	Medium

(Source: SQL Server 2005 Books Online)  For a complete list of these items look here:
In addition, each new compatibility level offers a new list of reserved keywords.  Here is a list of the new keywords for SQL Server 2005.

Compatibility level setting	Reserved keywords
90	PIVOT, UNPIVOT, REVERT, TABLESAMPLE
80	COLLATE, FUNCTION, OPENXML
70	BACKUP, CONTAINS, CONTAINSTABLE, DENY, FREETEXT, FREETEXTTABLE, PERCENT, RESTORE, ROWGUIDCOL, TOP
65	AUTHORIZATION, CASCADE, CROSS, DISTRIBUTED, ESCAPE, FULL, INNER, JOIN, LEFT, OUTER, PRIVILEGES, RESTRICT, RIGHT, SCHEMA, WORK

(Source: SQL Server 2005 Books Online)
If one of these keywords is being used and your database is set to this compatibly level the commands will fail.  To get around this you could put the keyword in either square brackets ([ ]) or use quotation marks (” “) such as [PIVOT] or “PIVOT”.