Geography, Geometry datatype in SQL 2012 with database compatability 2005(90)

Category: sql server spatial


GRaju.dba on Mon, 30 Jan 2017 21:14:48

Hi Team,

We are in a need of using datatypes Geography, Geometry in one of our locations table.

Goal is to get all locations in radius.

I am on SQL server 2012 but the DB was in 2005 compatibility mode.

I was able to create columns with geography datatype and populate values and run select queries. 

As this datatype was introduced in SQL 2008 version, I am able to create them in 2005 compatibility.

Are this still supported by Microsoft if we use them in SQL 2005 compatibility mode?


davidbaxterbrowne on Mon, 30 Jan 2017 22:00:09

Yes, it is supported.


GRaju.dba on Tue, 31 Jan 2017 15:01:15

Ok, thank you for the reply, I really appreciate it and helpful info!!

Can you clarify this?

1) New Data types which are introduced in new version of SQL can be used by upgrading the SQL instance, DB compatibility change is not Mandatory for that new datatype and supported by Microsoft? 

2) Do we have a document which Microsoft says like New features of newer version of SQL can be used without changing DB_compatibility please?

davidbaxterbrowne on Tue, 31 Jan 2017 15:10:27

>Do we have a document which Microsoft says like New features of newer version of SQL can be used without changing DB_compatibility please

There is no such document.  New features and data types _may_ not work without increasing the db compatibility level.  But many features new features work fine, and there is no difference in support based on database compatibility level.  It really depends on what had to be changed in the TSQL language to enable the new features.  Geography/Geometry are implemented as CLR types, and so should be fine.


GRaju.dba on Thu, 02 Feb 2017 14:05:19

Hi David, I really appreciate your response.

We want to start development work basing on this.

Can you confirm this?

Geography/Geometry datatypes can be used on SQL 2012 version even if the DB compatabily is 2005(90) .

They should work the same way as they work in 2008(100) or 2012(110) compatability.

Thanks in Advance, this will really help us to go ahead and make use of these columns in our DEV effort.

davidbaxterbrowne on Fri, 03 Feb 2017 03:21:15

Here's some implicit confirmation;

When the compatibility level is 100 or below in SQL Server 2012 then the geography data type has the following restrictions:

  • Each geography instance must fit inside a single hemisphere. No spatial objects larger than a hemisphere can be stored.

  • Any geography instance from an Open Geospatial Consortium (OGC) Well-Known Text (WKT) or Well-Known Binary (WKB) representation that produces an object larger than a hemisphere throws an ArgumentException.

  • The geography data type methods that require the input of two geography instances, such as STIntersection(), STUnion(), STDifference(), and STSymDifference(), will return null if the results from the methods do not fit inside a single hemisphere. STBuffer() will also return null if the output exceeds a single hemisphere.