Microsoft® SQL Server® 2012 Bible

38

Part I: Laying the Foundations

Setting the compatibility mode of a database attached to a SQL Server 2012 instance to a

lower compatibility level does not mean you can attach or restore this database to an older

version of SQL Server. The capability of setting a database at a lower compatibility level is

provided for backward compatibility as a temporary step to support legacy code.

For example, a database attached to a SQL Server 2012 instance and set in compatibility

mode level 90, enables you to run code compatible with SQL Server 2005, but you cannot

restore this database to a SQL Server 2005 instance. Table 3-4 shows the database compat-

ibility levels and corresponding SQL Server Version.

TABLE 3- 4 SQL Server Database Compatibility Levels

SQL Server Version Database Compatibility Level

SQL Server 6 60

SQL Server 6.5 65

SQL Server 7 70

SQL Server 2000 80

SQL Server 2005 90

SQL Server 2008 100

SQL Server 2008R2 100

SQL Server 2012 110

SQL Server 2012 supports only databases at compatibility levels 90, 100, and 110, meaning

it supports databases going back to SQL Server 2005 only.

To identify the compatibility level of a specifi c SQL Server database, you can query the

master.sys.sysdatabases table as follows:

SELECT cmptlevel

FROM master.sys.sysdatabases

WHERE name='AdventureWorks2012'

All databases attached or restored to a newer version of a SQL Server instance upgrade

automatically to the database version number that the newer SQL Server instance supports.

In the case of SQL Server 2012, all databases created, attached, or restored in a SQL Server

2012 instance convert to database version number 700.

As you see, database compatibility levels are not the same as database version numbers.

Perhaps Paul Randal described it best when he explained it this way: The database version

number is an internal number associated with a specifi c structure of a database’s sys-

tem tables containing metadata about various objects such as tables, columns, indexes,

allocations, and details about the relational and physical structure of the database.

c03.indd 38c03.indd 38 7/30/2012 4:10:00 PM7/30/2012 4:10:00 PM

http://www.it-ebooks.info