I had a question this week from someone who'd heard me say at SQL Connections (paraphrasing) "database compatibility level is mostly about query parsing" and was having trouble trying to forcibly attach a 2005 or 7.0 database to a 2000 server.
His 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.2050.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)
which I think is an intermediate SP2 build - not bothering to check. However, the database version is 611. You can't see this except using undocumented DBCC commands (which I'm not blogging - yet) 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.
[Edit: I stand corrected. Thanks to Trayce Jordan at Microsoft for pointing out that the database version will be returned using the following T-SQL:
USE master;
GO
SELECT DatabaseProperty ('dbccpagetest', 'version');
GO
/Edit]
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.065 = SQL Server 6.570 = SQL Server 7.080 = SQL Server 200090 = SQL Server 2005
You can see the compatibility level of all databases by doing:
SELECT name AS 'DB Name', compatibility_level AS 'Compatibility Level'
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.
A database created by a more recent version of Microsoft SQL Server cannot be attached or restored to an earlier version. This restriction is there because an older version cannot know about file format changes that were introduced in the newer release.
If you attempt to attach a database to an earlier version, you will get SQL Server error
948 with the internal version numbers listed in the error message text.
The internal database versions for SQL Server aren't clearly documented in the Microsoft documentation.
| SQL Server Version | Internal Database Version | Database Compatibility Level |
|---|---|---|
| SQL Server 2014 | 782 | 120 |
| SQL Server 2012 | 706 | 110 |
| SQL Server 2012 CTP1 (a.k.a. SQL Server 2011 Denali) | 684 | 110 |
| SQL Server 2008 R2 | 660 / 661 | 100 |
| SQL Server 2008 | 655 | 100 |
| SQL Server 2005 SP2+ with VarDecimal enabled | 612 | 90 |
| SQL Server 2005 | 611 | 90 |
| SQL Server 2000 | 539 | 80 |
| SQL Server 7.0 | 515 | 70 |
| SQL Server 6.5 | 408 | 65 |
| SQL Server 6.0 | ? | 60 |
No comments:
Post a Comment