Spatial Index is NOT used when SUBQUERY used

I have found the following link to be invaluable when working with and tuning SQL Server Spatial indexes:  https://technet.microsoft.com/en-us/library/bb895265.aspx

However, the link is not as clear as it could be about the Spatial index selections made by the SQL Server query processing.  Here are a few additional tidbits that may assist you.  (Note:   Similar tips may apply to non-Spatial queries as well.)

1. The Spatial method must be on the left side of the predicate (where clause)

       col.STIntersects(@val) = 1   --    Can use the index if costing is appropriate
       1 = col.STIntersects(@val)   --    Unlikely to use index, use previous form

2. The value passed to the spatial method must be ‘constant like’

       col.STDistance(@val) = 1 * 10000   --    Can use the index if costing is appropriate
       col.STDistance(@val / 10000) = 1   --    Unlikely to use index, use previous form

3. Extension of #2 for more complex operations

/* The subquery form does not consider the index */
Select * from Spat where col2.STIntersects((select col2 from Spat where Id = 23 and col2 is not null))=1

/* Using index hint - getting an error message for this query form */
-- Msg 8622, Level 16, State 1, Line 1
-- Query processor could not produce a query plan because of the hints defined in this query.
Select * from Spat with (index(SpatIDX)) where col2.STIntersects( (select col2 from Spat where Id =23) ) = 1

/* Variable or Join forms attempt to use the index */
Declare @i geography
Set @i = (select col2 from Spat where Id =23)
Select * from Spat  where col2.STIntersects(( @i))=1  order by Id

Select s1.* from Spat as s1
join Spat as s2 ON
      s1.col2.STIntersects(s2.col2) = 1
   and s2.Id = 23
order by s1.Id

As you can see the variable or join syntax is a construct the SQL Server query processing can evaluate for Spatial index usage where as the subquery is generally not considered.

Be sure to check the form of your queries to make sure the indexes are properly considered.

Bob Dorr - Principal SQL Server Escalation Engineer