:: infotechGuyz.com ::
> For the IT community by the IT community

• BlackBerry Server (BES)
• Excel Macro/VBA
• Exchange 2007
• Exchange 2010
• Lync 2010
• Scripting/Command-line
• SQL Server 2008
• SQL Server 2012
• Team Foundation Server
• VMware
• Windows 7
• Windows 8
• Windows Server 2008
• Windows Server 2012

Database Compatibility Level in SQL Server 2008 R2

When it comes to Database Compatibility Level Migrating pre-2008 databases into SQL Server 2008, there are many questions of compatibility issues and database compatibility levels. Compatibility level is based on a per-database setting which controls T-SQL execution behavior with concern to SQL Server's versioning system.

To the extent of being flexible, the T-SQL execution engine has the capacity to switch between varying, version-dependent behaviors according to the current compatibility-level setting. When a database is upgraded to SQL Server 2008 from an earlier version of SQL Server, the database keeps the existing compatibility level only if it is at least 80 (SQL Server 2000). If you upgrade a database with compatibility level less than 80 set the database to compatibility level 80.

Compatibility level affects behaviors for only the specified database and not for the server entirely. You should understand one important point about database compatibility levels is that the database compatibility-level setting is meant to provide only partial backward compatibility with previous versions of SQL Server. It does not prevent using the new T-SQL features which are available in SQL Server 2008 and includes features such as new data types and statements.

The compatibility-level setting is mainly made available as a temporary migration support to work around version differences in the behaviors controlled by the relevant compatibility-level setting. Basically, it allows T-SQL code that uses deprecated features or anticipates pre-100 level behaviors for certain commands to continue working as it did in the prior version of SQL Server. If you use the compatibility-level setting, you should not view this as a permanent solution. You should use the compatibility-level setting only until the T-SQL code that are affected by behavioral differences in SQL Server 2008 can be converted to properly work in SQL Server 2008. Then use ALTER DATABASE to change the compatibility level to 100. You can use this option to set the compatibility level for a particular database. To view the current compatibility level of a database, query the compatibility_levelcolumn in the sys .databases catalog view:

selectcompatibility_level from sys.databases where name = db_name()
go