In a previous post, I discussed a little about the approach that SQL Server 2008 uses when performing operations using the geometry and geography spatial datatypes, and the precision implications of performing calculations on a fixed size integer grid. Although not mentioned explicitly in that post, the points covered apply equally to SQL Server 2008/R2 and to the current spatial functionality in SQL Azure.
The integer grid, which is limited to 27bit resolution in SQL Server 2008/R2/Azure, is increased to 48bit resolution in SQL Server Denali. As described in the Microsoft White Paper, “New Spatial Features in SQL Server Code-Named “Denali” Community Technology Preview 1”, this increased precision can assist in ensuring that the accuracy of input coordinates is preserved throughout spatial operations. For example, consider the following coordinate, which was processed using the STUnion() method in SQL Server 2008 but which was not involved in the resulting geometry.
(82.339026 29.661245) –> (82.339025999885052 29.662144999951124)
Here is the result of the same STUnion() method in SQL Server Denali:
(82.339026 29.661245) –> (82.339026 29.662145)
Certainly, the increased resolution resolves many of the issues I pointed out in my previous post. You can try the new increased precision in the current Denali CTP1, available for download from here. However, there is still no official announcement as to when we will expect to see Denali released for RTM.
But what is interesting is that, in the “What’s new in SQL Azure” MSDN page for May 2011, there is now a note that reads as follows:
Upcoming Increased Precision of Spatial Types: For the next major service release, some intrinsic functions will change and SQL Azure will support increased precision of Spatial Types. This will have an impact on persisted computed columns as well as any index or constraint defined in terms of the persisted computed column. With this service release SQL Azure provides a view to help determine objects that will be impacted by the change. Query sys.dm_db_objects_impacted_on_version_change (SQL Azure Database) in each database to determine impacted objects for that database.
The date of the “next major service release” for SQL Azure, just like the date for the release of SQL Denali, has not been announced. But this is certainly indication that the spatial functionality is signed off and, interestingly, we might see the benefits of increased spatial precision appearing in SQL Azure before SQL Denali (either that, or Microsoft are planning a simultaneous release for cloud and non-cloud, and SQL Denali RTM date might be closer then we think….)