I have a table with a
geometry column. For one record, there is only one
Point stored. A spatial index has been created but queries searching for the nearest location do not use this index resulting in bad performance.
--Create the table create table Location( LocationID int not null identity(1,1) primary key, LocationPoint geometry ) --add records declare @counter int =0 WHILE @counter<150000 BEGIN set nocount on --select set @counter =@counter +1 declare @RandomLocation geometry=geometry::Point(RAND() *1000, RAND() *1000, 0) insert into Location(LocationPoint) values (@RandomLocation) END --create index CREATE SPATIAL INDEX SPATIAL_StructureBE ON dbo.Location(LocationPoint) USING GEOMETRY_GRID WITH ( BOUNDING_BOX = (xmin = 0.0, ymin = 0.0, xmax = 1000, ymax = 1000), GRIDS = ( LEVEL_1 = MEDIUM, LEVEL_2 = MEDIUM, LEVEL_3 = MEDIUM, LEVEL_4 = MEDIUM), CELLS_PER_OBJECT = 16, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) --Search, this query should use the index but it doesn't declare @CurrentLocation geometry=geometry::Point(24,50, 0) select top 1 * from Location order by LocationPoint.STDistance(@CurrentLocation) asc