SQL Azure Compatibility Level

Question

I thought that SQL Azure was built on top of SQL Server 2012, but the compatibility level when you create a new database is 100 (SQL Server 2008's compatibility level), not 110.

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

I tried changing it to 110 using the two methods that I am aware of:

ALTER DATABASE Test SET COMPATIBILITY_LEVEL = 110;
--> Incorrect syntax near 'SET'.

EXEC sp_dbcmptlevel 'Test', 110;
--> Could not find stored procedure 'sp_dbcmptlevel'.

The reason this is an issue for me is because SQL 2008 doesn't support geography shapes which cross hemispheres, so if you zoom out a map to see the world and try to store the bounds of the map it will fail. Pretty silly right?

I thought that this would not be an issue in SQL Azure, because it has been fixed in SQL Server 2012, but when I try to create a shape that crosses hemispheres I get the following error:

Microsoft.SqlServer.Types.GLArgumentException: 24205: The specified input does not represent a valid geography instance because it exceeds a single hemisphere. Each geography instance must fit inside a single hemisphere. A common reason for this error is that a polygon has the wrong ring orientation. To create a larger than hemisphere geography instance, upgrade the version of SQL Server and change the database compatibility level to at least 110.

So it is telling me to change the compatibility level, like it knows that this has been fixed already, but I can't figure out how to do that in SQL Azure. Anyone have a suggestion of something to try? Or let me know if it just is not possible right now?

Answer 1

It is hard to say if the current SQL Azure is based on SQL Server 2008 or 2012 however November 2011 update adds lots of new feature to it from SQL Server 2008 and 2012. More Info on Database Engine Versions:

Updated Engine Version: This release updates the underlying SQL Azure database engine version from 11.0.1477.26 to 11.0.1750.34 as it is rolled out across data centers.

The following link talks about what is and what not supported with SQL Azure comparative to SQL Server 2008 and SQL Server 2008 R2:

http://msdn.microsoft.com/en-us/library/windowsazure/ff394115

The following links adds more info about what new Programmability Enhancements are added in SQL Azure from SQL Server 2012:

http://msdn.microsoft.com/en-us/library/windowsazure/hh987034.aspx

Answer 2

UPDATE: August 2015 Azure Sql Database V12 has a default compatibility level of 120 with possibility to go up to 130 or down using ALTER DATABASE SET COMPATIBILITY_LEVEL syntax.