I have a large table (10's of millions of rows per day) which is partitioned by time and includes a geography column with a spatial index.
The following query,
DECLARE @polygon geography = geography::STGeomFromText('POLYGON((-2.797749 30.595396, 40.908162 30.595396, 40.908162 40.523745, -2.797749 40.523745, -2.797749 30.595396))', 4326);
SELECT * FROM Locations WHERE Time > '2021-01-01T23:59:59.997' AND Time <= '2021-01-02T23:59:59.997' AND @polygon.STContains(Location) = 1;
according to the SSMS query statistics, first uses the spatial index but doesn't apply partition elimination to the index which will result in looking over billions of rows.
According to the following, https://support.microsoft.com/en-us/topic/kb4089950-update-to-support-partition-elimination-in-query-plans-that-have-spatial-indexes-in-sql-server-2016-and-2017-d1f87c36-4738-cd04-1e2c-baf455288f50, an update was released to allow this to function properly but even after applying I'm still having the issue of no partition elimination occurring on the spatial index.
Partition elimination works as expected for partitioned views, but I'd rather not use them if possible.
Is it possible to use partition elimination using spatial indexes in SQL Server 2016?
Thanks in advance