Como criar corretamente um índice espacial e escrever uma consulta SARGable que aproveite esse índice espacial?

A documentação que você vincula diz

Os índices espaciais suportam as seguintes formas de predicado:

geografia1.STIntersects(geografia2)= 1

geografia1.STEquals(geografia2)= 1

geografia1.STDistance(geografia2) < número

geografia1.STDistance(geografia2) <= número

Portanto, enquanto você estiver procurando por casos em que o resultado da função de distância seja menor que (ou menor ou igual a) algum número, você não deverá ver uma mensagem sobre um "comparador fornecido no predicado" inválido.

(As condições também devem ser expressas como acima = por exemplo, 1 > Coordinates.STDistance(@Point)falha ao vincular explicitamente para forçar o índice espacial nomeado com "Motivo: Não foi possível encontrar o método espacial binário necessário em uma condição")

O índice espacial pode ser forçado com ou uma dica FORCESEEKexplícita ( Fiddle ). A dica de índice explícita parece dar informações mais úteis no caso de uma falha.INDEX

Se você forçar o índice espacial no seu caso, o plano de execução chama a [GeodeticTessellation]função com valor de tabela passando parâmetros @Point, 768, 9, 1, @Distancepara obter os valores a serem usados ​​no índice espacial seek 768é o padrão SPATIAL_WINDOW_MAX_CELLSe pode ser substituído pela dica correspondente. Não tenho certeza do que eles 9,1correspondem.

Isso emite 8 linhas que são então usadas para buscar no índice espacial, um agregado colapsa o resultado dessa junção para produzir uma linha por PK que é então usada em uma pesquisa de índice clusterizado contra a tabela base e, finalmente, há um filtro com um predicado residual. A regra do otimizador responsável por isso é SpatialIntersectFilterOverGridIndex.

No caso dos seus dados de exemplo, isso custa 45 vezes mais do que apenas escanear a tabela e avaliar o predicado em relação a cada linha (custos de subárvore de 0.0042838vs 0.193235)

Seu fiddle original tinha uma expressão WHERE Coordinates.STDistance(@Point) IS NOT NULL. Este não é um dos dois predicados comparadores suportados para STDistanceque podem usar um índice espacial (exceto se a consulta também atender a todas as condições para uma ordenação de "vizinho mais próximo" ).

1. Um índice espacial deve estar presente em uma das colunas espaciais e o STDistance()método deve usar essa coluna nas cláusulas WHEREand ORDER BY.
2. A cláusula TOP não pode conter uma PERCENTdeclaração.
3. A cláusula WHERE deve conter um STDistance()método.
4. Se houver vários predicados na WHEREcláusula, então o predicado que contém STDistance()o método deve ser conectado por uma conjunção AND aos outros predicados. O STDistance()método não pode estar em uma parte opcional da cláusula WHERE.
5. A primeira expressão na cláusula ORDER BY deve usar o STDistance()método.
6. A ordem de classificação da primeira STDistance()expressão na ORDER BYcláusula deve ser ASC.
7. Todas as linhas para as quais STDistanceos retornos NULLdevem ser filtrados.

Isso essencialmente acaba adicionando um predicado implícito.

O plano de execução para

SELECT TOP(@N) Coordinates.STDistance(@Point), * FROM dbo.GeoTest WITH (INDEX =IX_TestSpatial) 
WHERE Coordinates.STDistance(@Point) IS NOT NULL
ORDER BY Coordinates.STDistance(@Point)

Funciona de forma semelhante a este pseudocódigo

SELECT TOP (@N) *
FROM (VALUES 
(0,      86.153535551610716e0),
(1,     172.30707110322143e0),
(2,     344.61414220644286e0),
(3,     689.22828441288573e0),
(4,    1378.4565688257715e0),
(5,    2756.9131376515429e0),
(6,    5513.8262753030858e0),
(7,   11027.652550606172e0),
(8,   22055.305101212343e0),
(9,   44110.610202424687e0),
(10,  88221.220404849373e0),
(11, 176442.44080969875e0),
(12, 352884.88161939749e0),
(13, 705769.76323879499e0),
(14,1411539.5264775900e0),
(15,2823079.0529551799e0),
(255,1.7976931348623157e+308 /*MAX_FLOAT*/)
) V(Level1003, MaxDistance1004)
CROSS APPLY 
(
SELECT TOP (@N) *,
                Coordinates.STDistance(@Point) AS Dist,
                COUNT(*) OVER (PARTITION BY Level1003) AS CountMatchedThisLevel
FROM dbo.GeoTest WITH(FORCESEEK)
WHERE Coordinates.STDistance(@Point) <= MaxDistance1004
ORDER BY Dist
) CA
WHERE CountMatchedThisLevel >= @N OR Level1003 = 255
ORDER BY Level1003, Dist;

onde ele tenta aumentar exponencialmente as distâncias em ordem e pode parar na iteração encontrando pelo menos @Nresultados e retornar os TOP @Nresultados para aquele nível (ou os resultados para o nível final se nenhuma das distâncias tentadas encontrou @Nresultados)