It has been well documented and is well known that SQL Server supports certain older versions of SQL Server in a compatibility mode.  This setting is something that can be configured on the database properties level.  You can quickly change to an older compatibility level or revert the change to a newer compatibility level.

Changing the compatibility level is sometimes necessary.  Knowing what compatibility modes are available for each database is also somewhat necessary.  The common rule of thumb has been the current version and two prior versions.  But even with that, sometimes it is warm and fuzzy to be able to see the supported versions in some sort of format other than through the GUI for database properties.

Sure, one could go and check Books Online.  You can find that information there.  Or you could fire up this script and run with the output (as a guideline).

[codesyntax lang="tsql"]

WITH compatversions AS (
SELECT 65 AS MajorVersion ,'SQL Server 6.5' AS ServerVersion
UNION
SELECT 70,'SQL Server 7.0' 
UNION
SELECT 80 , 'SQL Server 2000' 
UNION 
SELECT 90 , 'SQL Server 2005' 
UNION 
SELECT 100 , 'SQL Server 2008/R2' 
UNION
SELECT 110 , 'SQL Server 2012' 
UNION 
SELECT 120 , 'SQL Server 2014' 
)
SELECT TOP 3 ServerVersion,MajorVersion
,ServerVersion + ' ('+ CONVERT(VARCHAR(3),MajorVersion) +')' AS DropDownFormat
FROM compatversions
WHERE MajorVersion IN (
SELECT TOP 3 MajorVersion 
FROM compatversions
WHERE MajorVersion <= CONVERT(INT,CAST(@@microsoftversion/ 0x1000000 AS VARCHAR(3)) + '0')
ORDER BY MajorVersion DESC) 
ORDER BY MajorVersion ASC;

[/codesyntax]

This script will return results such as the following.

And if we wanted to see the results for a SQL Server 2014 installation, we would see the following.

The output is displayed in the same format you might see it if you were to use the Database Properties GUI.  That said, if you are using the GUI in SQL Server 2014, you might run into the following.

Notice the additional compatibility level for SQL 2005?  If you check the documentation, you will probably find that compatibility level 90 is not supported in SQL 2014.  In fact it says that if a database is in 90 compatibility, it will be upgraded to 100 automatically (SQL 2008).  You can find all of that and more here.

If you tried to select compatibility 90, you might end up with an error.  If you are on 2014 CTP2, you will probably be able to change the compat level without error.

Anyway, this is the message you might see when trying to change to compatibility 90.

They sometimes say that “seeing is believing.”  Well in this case, you may be seeing a compatibility level in the 2014 GUI that just isn’t valid.  Keep that in mind when using the GUI or trying to change compatibility modes.