Azure SQL Compatibility Level

Question

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

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

I tried changing it to 110 using two methods that I know 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 a problem for me is because SQL 2008 does not support geography forms that cross hemispheres, so if you zoom out on the map to see the world and try to keep the map borders, it will fail. Pretty stupid right?

I thought this would not be a problem in SQL Azure because it was fixed in SQL Server 2012, but when I try to create a shape that intersects the hemisphere, I get the following error:

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

So, they tell me that I am changing the compatibility level, since he knows that this has already been fixed, but I can’t figure out how to do this in SQL Azure. Anyone have a suggestion to try? Or let me know if this is simply not possible now?

Answer 1

It's hard to say whether the current Azure SQL is based on SQL Server 2008 or 2012, but the November 2011 update adds new features to it with SQL Server 2008 and 2012. More information about the database engine versions:

Updated version of the module: this release updates the base version of the Azure SQL Database Engine from 11.0.1477.26 to 11.0.1750.34 when it is pumped through data centers.

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

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

The following links add more information about what new programming enhancements are being added to 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 the ability to jump to 130 or down using the ALTER DATABASE SET COMPATIBILITY_LEVEL syntax.