By: Ben Snaidero
Overview
In this section we look at another type of SQL Server index, spatial indexes.
What is a spatial index?
A spatial index is another special index type that is built to accommodate adding indexes on columns created using the spatial datatypes geography and geometry. As was the case with an XML index, spatial indexes also require that the database table that you are creating the spatial index on also has a clustered primary key index defined.
Why use a spatial index?
When it comes to spatial data you are generally testing if two points and/or areas intersect or are within a certain distance. The benefit of having a spatial index created on your column is it allows the query to easily prune/skip over column values that there is no chance of intersection. When creating the index there are options available that increase the accuracy of the index but with that comes the drawback that the index will use more space.
How to create a spatial index?
In order to create a spatial index we will use of the tables in our sample database and create the index using the CREATE SPATIAL INDEX command. Note that this is not just a clause of the "CREATE INDEX" command. It's actually a command with many other options specific to spatial indexes. You can read more details on those options here.
CREATE SPATIAL INDEX IX_Address_SpatialLocation ON Person.Address(SpatialLocation);
Confirm Index Usage
The following query should use the spatial index in order to determine the 7 closest points to the specified location. Here is the TSQL.
DECLARE @g geography = 'POINT(-121.626 47.8315)'; SELECT TOP(7) SpatialLocation.ToString(), City FROM Person.Address WHERE SpatialLocation.STDistance(@g) IS NOT NULL ORDER BY SpatialLocation.STDistance(@g);
We can check the EXPLAIN plan for this query and confirm that it is indeed using the new index we just created.
