次の方法で共有

What's the difference between database version and database compatibility level?

  • [アーティクル]

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.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:

  • 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.

Hope this helps!

Comments

  • Anonymous
    June 18, 2009
    PingBack from http://thestoragebench.info/story.php?id=9688

  • Anonymous
    July 16, 2011
    I mention the difference between all the different points of SQL Server version wise to understand easily. Sql Server 2000:

  1. Query Analyser and Enterprise manager are separate.
  2. No XML datatype is used.
  3. We can create maximum of 65,535 databases.
  4. Nill
  5. Nill
  6. Nill
  7. Nill
  8. Nill
  9. Nill
  10. Nill
  11. Nill
  12. Nill
  13. Can’t compress the tables and indexes.
  14. Datetime datatype is used for both date and time.
  15. No varchar (max) or varbinary(max) is available.
  16. No table datatype is included.
  17. No SSIS is included.
  18. CMS is not available.
  19. PBM is not available. more..........................                 sharepointsolution2010.blogspot.com/.../2000-2005-2008-sql-server-version.html