Yesterday’s blog post about Azure SQL Database V12 mentioned that one of the features I was particularly interested in seeing/testing were the spatial features. Interestingly, this was not even mentioned as an enhancement in the technical “What’s New” page. This may be due to the fact that the only place where a list of the limitations of existing Azure SQL Database spatial implementation was documented is Ed Katibah’s article about “Updated Spatial Features in the SQL Azure Q4 2011 Service Release“. Q4 2011 was the last update for ASD spatial that I was aware of. As an example, the Transact-SQL books online page for CREATE SPATIAL INDEX makes no mention of the fact that ASD doesn’t support AUTO_GRID spatial indexes and spatial index compression.
So I fired up an S1 instance of both a current (V11) database and a new V12 preview database (to ensure that I hadn’t missed an update to V11), and tested out all the features that Ed mentioned. They were never updated in pre-V12, but EVERYTHING is there in V12. Very cool!
You can refer to Ed’s article for a complete list (not supported in V11, supported in V12 and SQL Server 2012/2014), but here’s some of the highlights:
Spatial objects larger than a hemisphere
FULLGLOBE, circular/curve types, and associated methods
AutoGrid spatial index
Compression for spatial indexes
Spatial_Windows_Max_Cells query hint
Spatial Analysis Stored Procedures and Histograms
As long as I was experimenting in a V12 database, I thought I’d try the Codeplex SQL Spatial Tools (http://sqlspatialtools.codeplex.com/) an extention library for spatial built using SQLCLR. As I’d suspected, you can run CREATE ASSEMBLY by with a file system or Azure blob storage reference. Tooling is coming soon, but until then, I had to catalog the assembly from the file system to a “box version” SQL Server and then use SSMS Object Explorer to locate and right-click on the “box” SQL Server Assembly and use “Script Assembly as CREATE” to obtain a “CREATE ASSEMBLY … [from bits]” statement. The create-from-bits worked fine. The failure message using “local DLL” syntax is kind of interesting: “Msg 6585, Level 16, State 1, Line 2 Could not impersonate the client during assembly file operation.”
The SQL Spatial Tools assembly uses SQLCLR UDT, UDAggs, and UDFs, all seemed to work fine. Using SQLCLR along with spatial enables writing speedy spatial manipulation functions because each operation on a spatial object is not a separate T-SQL statement. So these are two features (SQLCLR and SQL spatial) that work well together.
That’s it for now.