SQL Server 2008 compatibility with SQL Server 2005

Question

What is the best way to use SQL Server 2008 as a development database, but ensure that the database is compatible with SQL Server 2005?

Answer 1

This can be done via SQL Enterprise Manager or like this:

ALTER DATABASE <database> 
SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }

Use 90 for 2005 compatibility.

This replaces the functionality used for previous releases, the stored procedure sp_dbcmptlevel.

Answer 2

You can set the database compatibility level, but there is no guarantee that this will cover all your bases. For example the new data types will be available on the SQL 2008 system, but they won't be available on the SQL 2005 server.

The only way to guarantee that everything will work from Dev to Prod is to put SQL 2005 in development.

Answer 3

Don't forget that there are also behavioral differences between the two versions, and something on 2008 may perform differently (and insufficiently) than 2005 all other things equal - this will obviously depend on a lot of factors about your data and application.

You're better off developing against the lowest common denominator and testing against the newer versions.