MS SQL Server

Supported Data Types

The SQL Server data source provider follows the same rules as the JDBC data source provider when mapping a non-spatial JDBC type to Spectrum Spatial’s type system. See the SQL Server documentation for which SQL Server data types are mapped to JDBC.

The SQL Server geometry and geography data types are the spatial data equivalent to Spectrum Spatial's supported data types: Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon, and GeometryCollection.

Determining the spatial metadata is done in several steps. The first step looks at the MAPINFO_MAPCATALOG table in the MAPINFO schema and if an entry exists identifies the geometry column in the SQL Server table and the style column. If the table is identified as being an X/Y table then the two columns identified as the X and Y columns will be "fused" together into a geometry column in the Spatial table with the SRID of the coordinate system specified in the MAPINFO_MAPCATALOG. If the MAPINFO_MAPCATALOG does not exists or the table to be opened does not have an entry then the INFORMATION_SCHEMA.TABLES table is consulted for the name of the spatial column. The SRID of the column is taken from the first non-null geometry in the table.

Primary Key

Spectrum Spatial follows the same rules as the JDBC data source provider for determining the primary key.

Read-Only Columns

Spectrum Spatial follows the same rules as the JDBC data source provider in determining whether a column is read-only. It also marks Identity columns as read-only.

MI SQL Optimizations

MS SQL Server supports the same non-spatial functions and operators as the JDBC data source provider.

MS SQL Server supports the following spatial operators:

For more information see the appendix Delegation to Data Source Providers.

Volatility

For SQL Server, volatility is any change to the table's schema, such as adding or dropping a column or changing an index on a column. If it has changed, Spectrum Spatial will flush the metadata about the table from the cache and reload it before proceeding with the data access operation. See Data Source Volatility for more information.

XY Table Support

SQL Server tables can be accessed as XY tables. See XY Table Support.

GeoIndexed Table Support

Named tables that are geoindexed (that is, they have a geohash column or attribute) are supported in SQL Server. See Support for Spatial Aggregation.

Support for Nearest Filter for SQL Server

The Find Nearest query is delegated to the SQL Server database if the data type of column is a geometry.

Improving Performance for SQL Server

To improve performance for very large SQL server tables, use sendStringParametersAsUnicode=False when creating connection strings to a SQL Server database in Spectrum Spatial Manager.

If your SQL server table uses CHAR, VARCHAR, and LONGVARCHAR data types rather than NCHAR and NVARCHAR, we recommend using the property sendStringParametersAsUnicode=False when creating connection strings to a SQL Server database in Spectrum Spatial Manager. If the sendStringParametersAsUnicode property is set to false, string parameters in SQL where clauses are sent to the server in non-Unicode format, such as ASCII/MBCS instead of Unicode. This will avoid string data being queried from the database from being converted to Unicode while performing the query, and can also significantly improve performance for large tables with millions of records.

Further details for this and other SQL server JDBC parameters can be found here https://docs.microsoft.com/en-us/sql/connect/jdbc/setting-the-connection-properties.