SQL Server Versions And Compatibility Levels

When working with a number of different SQL Server versions on various projects I’ve been a bit confused about what exactly I’ve been dealing with. I wanted a single table with all of the names, versions and compatibility levels all in one place. Here’s the result:

Release Version Number Compatibility Version
SQL Server 2000 8.x 80
SQL Server 2005 9.x 90
SQL Server 2008 10.0 100
SQL Server 2008 R2 10.5 100
SQL Server 2012 11.x 110

You can determine your version of SQL Server in a number of ways. Inside of SQL Server Management Studio the Database Engine’s node in object explorer should display the version number. Within a query window you can also run the following command:

Select @@version

A problem arises if you are working with a database in a shared hosting environment in that even though the engine hosting your database may be SQL Server 2008 R2 the compatibility level on your database may be set at SQL Server 2000. This allows for greater backwards compatibility with installed solutions even while the provider is able to update their servers. Find your database’s compatibility level by running the following command:

SELECT compatibility_level
FROM sys.databases WHERE name = '';

If you need additional functionality that your server provides but that is excluded due to the compatibility level of the database you can change this via this command:

ALTER DATABASE
SET COMPATIBILITY_LEVEL = 110;

If your database is in a hosted environment you’ll have to ask that your provider make this change.

This entry was posted in Programming and tagged . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *