This has been causing some problems on the various groups and forums over the last few days so I thought I’d repost this from my old Storage Engine blog. The questions have been around attaching 2005 databases to 2000 servers – even databases that are in 80 compat mode – and it doesn’t work. Why? The confusion is between database compatibility level and database version. Here’s a quick explanation of the difference. Database version The database version is a number stamped in the boot page of a database that indicates the SQL Server version of the most recent SQL Server instance the database was attached to. The database version number does not equal the SQL Server version. For example, doing the following: SELECT
@@version;
GO
on one SQL Server instance on my laptop returns:
Microsoft SQL Server 2005 – 9.00.3054.00 (Intel X86) Feb 13 2007 23:02:48 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
However, the database version is 611. You can see the database version but if you attach a database from an earlier version of SQL Server, you’ll see these numbers in the error log as SQL Server reports what upgrade steps its doing. You can also see by doing the following:
master;
USE
GO
SELECT
DatabaseProperty (‘dbccpagetest’, ‘version’);GO
Some things to note about database version:
- SQL Server is not up-level compatible. You cannot attach a database that was created on (or has been upgraded to) SQL Server 2005 to any earlier version of SQL Server (also true for trying to attach a 2000 database to 7.0, and so on).
- You cannot attach a database that was created on an earlier version without going through the proper upgrade procedures. Forcibly attaching a database using various hacky methods will result in all kinds of weird errors, and possibly crashes.
Database compatibility level
The database compatibility level determines how certain database behaviors work. For instance, in 90 compatibility, you need to use the OUTER JOIN syntax to do an outer join, whereas in earlier compatibility levels, you can use ‘*=’ and ‘=*’. Contrary to popular myth, all of the behavioral differences ARE documented – in the Books Online section for sp_dbcmptlevel – the SP used to set the compatibility level.
There are 5 supported compatibility levels support by SQL Server 2005:
60 = SQL Server 6.0
65 = SQL Server 6.5
70 = SQL Server 7.0
80 = SQL Server 2000
90 = SQL Server 2005
You can see the compatibility level of all databases by doing:
name AS ‘DB Name’, compatibility_level AS ‘Compatibility Level’
SELECT
FROM master.sys.databases;
GO
Some things to note about compatibility levels:
- A database created on SQL Server 2005 will have a default compatibility level of 90, unless the model database has a different compatibility level, in which case the new database inherits the compatibility level of model.
- New features may work under older compatibility levels but beware of SET options.
- An upgraded database retains its compatibility level. For example, a database that was created on SQL Server 2000, and didn’t have its compatibility level altered, will stay in 80 compatibility level when its upgraded to SQL Server 2005.
Summary
This was just a quick – and by no means comprehensive – explanation of the difference between the two terms. Basically, there’s no relationship between them.