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:
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:
SELECT
name AS 'DB Name',compatibility_level
AS 'Compatibility Level'FROM
master.sys.databases;GO
Some things to note about compatibility levels:
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.
Hope this helps!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.