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:
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:
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:
SET COMPATIBILITY_LEVEL = 110;
If your database is in a hosted environment you’ll have to ask that your provider make this change.