SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.GeoTest (GeoId INT PRIMARY KEY IDENTITY(1,1), Coordinates GEOGRAPHY);

INSERT INTO dbo.GeoTest (Coordinates)
VALUES (GEOGRAPHY::STGeomFromText('POLYGON ((-73.7920290001574 40.7110690002396, -73.7910140000061 40.7114330001657, -73.7902149997648 40.7117169998112, -73.7894130000489 40.7119980001503, -73.7890120001909 40.7120730001493, -73.7889589995891 40.7120719998775, -73.7888749999235 40.7120669998801, -73.788786000235 40.7120589997473, -73.7886399997469 40.7120360002965, -73.7885240002942 40.7120100000196, -73.7884349997075 40.7119850000056, -73.7878470002517 40.7118109999209, -73.7874129998835 40.7118490001739, -73.7869960001319 40.7119019997558, -73.7865750001824 40.7119719998585, -73.7861600000808 40.7120600000193, -73.7857519996517 40.7121640003071, -73.7853530003419 40.7122839999658, -73.7850289995785 40.711571000044, -73.7847209996069 40.7109220001244, -73.7844560001913 40.7103729998545, -73.7844100001606 40.7102769998409, -73.785304999763 40.7100630002489, -73.7850369999744 40.709502000068, -73.7842899996117 40.7079309998188, -73.7848129996684 40.7079320001528, -73.785561999681 40.7079360001268, -73.7857700000934 40.7079340001398, -73.786739000214 40.7078919997191, -73.7877190002052 40.7076610003368, -73.7880399996972 40.7075709998122, -73.7886909998005 40.7074680001824, -73.7895700004077 40.7072220002897, -73.7897500003328 40.7071709997166, -73.7906530003311 40.7087259998055, -73.7911439997024 40.7095660001563, -73.791599999812 40.7103440000778, -73.7920290001574 40.7110690002396))', 4326));
1 rows affected

CREATE SPATIAL INDEX IX_TestSpatial ON dbo.GeoTest (Coordinates);
SET SHOWPLAN_XML ON;
DECLARE @Point GEOGRAPHY = 'Point(40.755656 -73.587627)';

SELECT Coordinates
FROM dbo.GeoTest
WHERE Coordinates.STDistance(@Point) <= 1;


Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4223.1"><BatchSequence><Batch><Statements><StmtSimple StatementText="DECLARE @Point GEOGRAPHY = &apos;Point(40.755656 -73.587627)&apos;;&#xa;&#xa;" StatementId="1" StatementCompId="1" StatementType="ASSIGN" RetrievedFromCache="false"></StmtSimple><StmtSimple StatementText="SELECT Coordinates&#xa;FROM dbo.GeoTest&#xa;WHERE Coordinates.STDistance(@Point) &lt;= 1" StatementId="2" StatementCompId="2" StatementType="SELECT" RetrievedFromCache="false" StatementSubTreeCost="0.00428378" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x9EBAD5B82FD1BE36" QueryPlanHash="0x229D01995BC1B2EC" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="150"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="32" CompileTime="31" CompileCPU="31" CompileMemory="296"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1428256"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2025-03-18T12:47:58.69" ModificationCount="0" SamplingPercent="100" Statistics="[IX_TestSpatial]" Table="[extended_index_581577110_384000]" Schema="[sys]" Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]"></StatisticsInfo><StatisticsInfo LastUpdate="2025-03-18T12:47:58.83" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000003_24927208]" Table="[extended_index_581577110_384000]" Schema="[sys]" Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]"></StatisticsInfo><StatisticsInfo LastUpdate="2025-03-18T12:47:58.83" ModificationCount="0" SamplingPercent="100" Statistics="[PK__GeoTest__EC445C5F9CFC1291]" Table="[GeoTest]" Schema="[dbo]" Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><RelOp NodeId="0" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="1" EstimateIO="0" EstimateCPU="0.00100068" AvgRowSize="4035" EstimatedTotalSubtreeCost="0.00428378" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[dbo]" Table="[GeoTest]" Column="Coordinates"></ColumnReference></OutputList><Filter StartupExpression="0"><RelOp NodeId="1" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="4035" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="1" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[dbo]" Table="[GeoTest]" Column="Coordinates"></ColumnReference></OutputList><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[dbo]" Table="[GeoTest]" Column="Coordinates"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[dbo]" Table="[GeoTest]" Index="[PK__GeoTest__EC445C5F9CFC1291]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp><Predicate><ScalarOperator ScalarString="[fiddle_eb4e4126495242ac81085bbaf18f7df2].[dbo].[GeoTest].[Coordinates].STDistance([@Point])&lt;=(1.0000000000000000e+000)"><Compare CompareOp="LE"><ScalarOperator><UDTMethod><CLRFunction Assembly="Microsoft.SqlServer.Types" Class="Microsoft.SqlServer.Types.SqlGeography" Method="STDistance"></CLRFunction><ScalarOperator><Identifier><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[dbo]" Table="[GeoTest]" Column="Coordinates"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="@Point"></ColumnReference></Identifier></ScalarOperator></UDTMethod></ScalarOperator><ScalarOperator><Const ConstValue="(1.0000000000000000e+000)"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></Filter></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

DECLARE @Point GEOGRAPHY = 'Point(40.755656 -73.587627)';

ASSIGN
ASSIGN
Estimated Operator Cost0 (0%)
Statement
DECLARE @Point GEOGRAPHY = 'Point(40.755656 -73.587627)';

SELECT Coordinates
FROM dbo.GeoTest
WHERE Coordinates.STDistance(@Point) <= 1
SELECT
SELECT
Cached plan size32 KB
Estimated Operator Cost0 (0%)
Estimated Subtree Cost0.0042838
Estimated Number of Rows1
Statement
SELECT Coordinates
FROM dbo.GeoTest
WHERE Coordinates.STDistance(@Point) <= 1
Filter
Cost: 23%
Filter
Physical OperationFilter
Logical OperationFilter
Estimated Execution ModeRow
Estimated Operator Cost0.0010007 (23%)
Estimated I/O Cost0
Estimated CPU Cost0.0010007
Estimated Subtree Cost0.0042838
Estimated Number of Executions1
Estimated Number of Rows1
Estimated Row Size4035 B
Node ID0
Output List
[fiddle_eb4e4126495242ac81085bbaf18f7df2].[dbo].[GeoTest].Coordinates
Predicate
[fiddle_eb4e4126495242ac81085bbaf18f7df2].[dbo].[GeoTest].[Coordinates].STDistance([@Point])<=(1.0000000000000000e+000)
Clustered Index Scan (Clustered)
[GeoTest].[PK__GeoTest__EC445C5F9CF…
Cost: 77%
Clustered Index Scan (Clustered)
Scanning a clustered index, entirely or only a range.
Physical OperationClustered Index Scan
Logical OperationClustered Index Scan
Estimated Execution ModeRow
StorageRowStore
Estimated Operator Cost0.0032831 (77%)
Estimated I/O Cost0.003125
Estimated CPU Cost0.0001581
Estimated Subtree Cost0.0032831
Estimated Number of Executions1
Estimated Number of Rows to be Read1
Estimated Number of Rows1
Estimated Row Size4035 B
OrderedFalse
Node ID1
Output List
[fiddle_eb4e4126495242ac81085bbaf18f7df2].[dbo].[GeoTest].Coordinates
Object
[fiddle_eb4e4126495242ac81085bbaf18f7df2].[dbo].[GeoTest].[PK__GeoTest__EC445C5F9CFC1291]
DECLARE @Point GEOGRAPHY = 'Point(40.755656 -73.587627)';

SELECT Coordinates
FROM dbo.GeoTest WITH(FORCESEEK)
WHERE Coordinates.STDistance(@Point) <= 1;


Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4223.1"><BatchSequence><Batch><Statements><StmtSimple StatementText="DECLARE @Point GEOGRAPHY = &apos;Point(40.755656 -73.587627)&apos;;&#xa;&#xa;" StatementId="1" StatementCompId="1" StatementType="ASSIGN" RetrievedFromCache="false"></StmtSimple><StmtSimple StatementText="SELECT Coordinates&#xa;FROM dbo.GeoTest WITH(FORCESEEK)&#xa;WHERE Coordinates.STDistance(@Point) &lt;= 1" StatementId="2" StatementCompId="2" StatementType="SELECT" RetrievedFromCache="false" StatementSubTreeCost="0.193235" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x6AAF771551E602B7" QueryPlanHash="0xA92DFD1EC234DFD3" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="150"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="48" CompileTime="0" CompileCPU="0" CompileMemory="320"><MemoryGrantInfo SerialRequiredMemory="1024" SerialDesiredMemory="1040" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1428256"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2025-03-18T12:47:58.69" ModificationCount="0" SamplingPercent="100" Statistics="[IX_TestSpatial]" Table="[extended_index_581577110_384000]" Schema="[sys]" Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]"></StatisticsInfo><StatisticsInfo LastUpdate="2025-03-18T12:47:58.83" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000003_24927208]" Table="[extended_index_581577110_384000]" Schema="[sys]" Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]"></StatisticsInfo><StatisticsInfo LastUpdate="2025-03-18T12:47:58.83" ModificationCount="0" SamplingPercent="100" Statistics="[PK__GeoTest__EC445C5F9CFC1291]" Table="[GeoTest]" Schema="[dbo]" Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><RelOp NodeId="0" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-06" AvgRowSize="4035" EstimatedTotalSubtreeCost="0.193235" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[dbo]" Table="[GeoTest]" Column="Coordinates"></ColumnReference></OutputList><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[dbo]" Table="[GeoTest]" Column="Coordinates"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference></OuterReferences><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-06" AvgRowSize="4036" EstimatedTotalSubtreeCost="0.192228" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[dbo]" Table="[GeoTest]" Column="Coordinates"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference></OutputList><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[sys]" Table="[extended_index_581577110_384000]" Column="pk0"></ColumnReference></OuterReferences><RelOp NodeId="2" PhysicalOp="Hash Match" LogicalOp="Aggregate" EstimateRows="1" EstimateIO="0" EstimateCPU="0.0224355" AvgRowSize="12" EstimatedTotalSubtreeCost="0.188941" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1003"></ColumnReference><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[sys]" Table="[extended_index_581577110_384000]" Column="pk0"></ColumnReference></OutputList><MemoryFractions Input="1" Output="1"></MemoryFractions><Hash><DefinedValues><DefinedValue><ColumnReference Column="Expr1003"></ColumnReference><ScalarOperator ScalarString="MAX([Expr1006])"><Aggregate Distinct="0" AggType="MAX"><ScalarOperator><Identifier><ColumnReference Column="Expr1006"></ColumnReference></Identifier></ScalarOperator></Aggregate></ScalarOperator></DefinedValue></DefinedValues><HashKeysBuild><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[sys]" Table="[extended_index_581577110_384000]" Column="pk0"></ColumnReference></HashKeysBuild><RelOp NodeId="3" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1000" EstimateIO="0" EstimateCPU="0.0001" AvgRowSize="12" EstimatedTotalSubtreeCost="0.166505" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[sys]" Table="[extended_index_581577110_384000]" Column="pk0"></ColumnReference><ColumnReference Column="Expr1006"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1006"></ColumnReference><ScalarOperator ScalarString="[fiddle_eb4e4126495242ac81085bbaf18f7df2].[sys].[extended_index_581577110_384000].[Attr]+[Attr]"><Arithmetic Operation="ADD"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[sys]" Table="[extended_index_581577110_384000]" Column="Attr"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Attr"></ColumnReference></Identifier></ScalarOperator></Arithmetic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="4" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1000" EstimateIO="0" EstimateCPU="0.00418" AvgRowSize="13" EstimatedTotalSubtreeCost="0.166405" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[sys]" Table="[extended_index_581577110_384000]" Column="Attr"></ColumnReference><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[sys]" Table="[extended_index_581577110_384000]" Column="pk0"></ColumnReference><ColumnReference Column="Attr"></ColumnReference></OutputList><NestedLoops Optimized="0"><OuterReferences><ColumnReference Column="Id"></ColumnReference><ColumnReference Column="LimitId"></ColumnReference></OuterReferences><RelOp NodeId="5" PhysicalOp="Table-valued function" LogicalOp="Table-valued function" EstimateRows="1000" EstimateIO="0" EstimateCPU="0.00100016" AvgRowSize="18" EstimatedTotalSubtreeCost="0.00100016" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Id"></ColumnReference><ColumnReference Column="Attr"></ColumnReference><ColumnReference Column="LimitId"></ColumnReference></OutputList><TableValuedFunction><DefinedValues><DefinedValue><ColumnReference Column="Id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Attr"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="LimitId"></ColumnReference></DefinedValue></DefinedValues><Object Table="[GeodeticTessellation]"></Object><ParameterList><ScalarOperator ScalarString="[@Point]"><Identifier><ColumnReference Column="@Point"></ColumnReference></Identifier></ScalarOperator><ScalarOperator ScalarString="(768)"><Const ConstValue="(768)"></Const></ScalarOperator><ScalarOperator ScalarString="(9)"><Const ConstValue="(9)"></Const></ScalarOperator><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator><ScalarOperator ScalarString="(1.0000000000000000e+000)"><Const ConstValue="(1.0000000000000000e+000)"></Const></ScalarOperator></ParameterList></TableValuedFunction></RelOp><RelOp NodeId="6" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="12" EstimatedTotalSubtreeCost="0.161225" TableCardinality="256" Parallel="0" EstimateRebinds="994.377" EstimateRewinds="4.62341" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[sys]" Table="[extended_index_581577110_384000]" Column="Attr"></ColumnReference><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[sys]" Table="[extended_index_581577110_384000]" Column="pk0"></ColumnReference></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[sys]" Table="[extended_index_581577110_384000]" Column="Attr"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[sys]" Table="[extended_index_581577110_384000]" Column="pk0"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[sys]" Table="[extended_index_581577110_384000]" Index="[IX_TestSpatial]" TableReferenceId="-1" IndexKind="Spatial" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><StartRange ScanType="GE"><RangeColumns><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[sys]" Table="[extended_index_581577110_384000]" Column="Id"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[Id]"><Identifier><ColumnReference Column="Id"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></StartRange><EndRange ScanType="LE"><RangeColumns><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[sys]" Table="[extended_index_581577110_384000]" Column="Id"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[LimitId]"><Identifier><ColumnReference Column="LimitId"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></EndRange></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp></ComputeScalar></RelOp></Hash></RelOp><RelOp NodeId="7" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="4035" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="1" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[dbo]" Table="[GeoTest]" Column="Coordinates"></ColumnReference></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="1" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[dbo]" Table="[GeoTest]" Column="Coordinates"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[dbo]" Table="[GeoTest]" Index="[PK__GeoTest__EC445C5F9CFC1291]" IndexKind="Clustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[dbo]" Table="[GeoTest]" Column="GeoId"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_eb4e4126495242ac81085bbaf18f7df2].[sys].[extended_index_581577110_384000].[pk0]"><Identifier><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[sys]" Table="[extended_index_581577110_384000]" Column="pk0"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp><RelOp NodeId="8" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="1" EstimateIO="0" EstimateCPU="0.00100138" AvgRowSize="9" EstimatedTotalSubtreeCost="0.00100254" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><Filter StartupExpression="1"><RelOp NodeId="9" PhysicalOp="Constant Scan" LogicalOp="Constant Scan" EstimateRows="1" EstimateIO="0" EstimateCPU="1.157e-06" AvgRowSize="9" EstimatedTotalSubtreeCost="1.157e-06" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><ConstantScan></ConstantScan></RelOp><Predicate><ScalarOperator ScalarString="CASE WHEN [Expr1003]&gt;(2) THEN SridMatch([fiddle_eb4e4126495242ac81085bbaf18f7df2].[dbo].[GeoTest].[Coordinates],[@Point]) ELSE [fiddle_eb4e4126495242ac81085bbaf18f7df2].[dbo].[GeoTest].[Coordinates].STDistance([@Point])&lt;=(1.0000000000000000e+000) END"><IF><Condition><ScalarOperator><Compare CompareOp="GT"><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(2)"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Intrinsic FunctionName="SridMatch"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[dbo]" Table="[GeoTest]" Column="Coordinates"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="@Point"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator></Then><Else><ScalarOperator><Compare CompareOp="LE"><ScalarOperator><UDTMethod><CLRFunction Assembly="Microsoft.SqlServer.Types" Class="Microsoft.SqlServer.Types.SqlGeography" Method="STDistance"></CLRFunction><ScalarOperator><Identifier><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[dbo]" Table="[GeoTest]" Column="Coordinates"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="@Point"></ColumnReference></Identifier></ScalarOperator></UDTMethod></ScalarOperator><ScalarOperator><Const ConstValue="(1.0000000000000000e+000)"></Const></ScalarOperator></Compare></ScalarOperator></Else></IF></ScalarOperator></Predicate></Filter></RelOp></NestedLoops></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

DECLARE @Point GEOGRAPHY = 'Point(40.755656 -73.587627)';

ASSIGN
ASSIGN
Estimated Operator Cost0 (0%)
Statement
DECLARE @Point GEOGRAPHY = 'Point(40.755656 -73.587627)';

SELECT Coordinates
FROM dbo.GeoTest WITH(FORCESEEK)
WHERE Coordinates.STDistance(@Point) <= 1
SELECT
SELECT
Cached plan size48 KB
Estimated Operator Cost0 (0%)
Estimated Subtree Cost0.193235
Estimated Number of Rows1
Statement
SELECT Coordinates
FROM dbo.GeoTest WITH(FORCESEEK)
WHERE Coordinates.STDistance(@Point) <= 1
Nested Loops
(Inner Join)
Cost: 0%
Nested Loops
For each row in the top (outer) input, scan the bottom (inner) input, and output matching rows.
Physical OperationNested Loops
Logical OperationInner Join
Estimated Execution ModeRow
Estimated Operator Cost0.0000045 (0%)
Estimated I/O Cost0
Estimated CPU Cost0.0000042
Estimated Subtree Cost0.193235
Estimated Number of Executions1
Estimated Number of Rows1
Estimated Row Size4035 B
Node ID0
Output List
[fiddle_eb4e4126495242ac81085bbaf18f7df2].[dbo].[GeoTest].Coordinates
Outer References
[fiddle_eb4e4126495242ac81085bbaf18f7df2].[dbo].[GeoTest].Coordinates
Expr1003
Nested Loops
(Inner Join)
Cost: 0%
Nested Loops
For each row in the top (outer) input, scan the bottom (inner) input, and output matching rows.
Physical OperationNested Loops
Logical OperationInner Join
Estimated Execution ModeRow
Estimated Operator Cost0.0000039 (0%)
Estimated I/O Cost0
Estimated CPU Cost0.0000042
Estimated Subtree Cost0.192228
Estimated Number of Executions1
Estimated Number of Rows1
Estimated Row Size4036 B
Node ID1
Output List
[fiddle_eb4e4126495242ac81085bbaf18f7df2].[dbo].[GeoTest].Coordinates
Expr1003
Outer References
[fiddle_eb4e4126495242ac81085bbaf18f7df2].[sys].[extended_index_581577110_384000].pk0
Hash Match
(Aggregate)
Cost: 12%
Hash Match
Use each row from the top input to build a hash table, and each row from the bottom input to probe into the hash table, outputting all matching rows.
Physical OperationHash Match
Logical OperationAggregate
Estimated Execution ModeRow
Estimated Operator Cost0.022436 (12%)
Estimated I/O Cost0
Estimated CPU Cost0.0224355
Estimated Subtree Cost0.188941
Estimated Number of Executions1
Estimated Number of Rows1
Estimated Row Size12 B
Node ID2
Output List
Expr1003
[fiddle_eb4e4126495242ac81085bbaf18f7df2].[sys].[extended_index_581577110_384000].pk0
Compute Scalar
Cost: 0%
Compute Scalar
Compute new values from existing values in a row.
Physical OperationCompute Scalar
Logical OperationCompute Scalar
Estimated Execution ModeRow
Estimated Operator Cost0.0001 (0%)
Estimated I/O Cost0
Estimated CPU Cost0.0001
Estimated Subtree Cost0.166505
Estimated Number of Executions1
Estimated Number of Rows1000
Estimated Row Size12 B
Node ID3
Output List
[fiddle_eb4e4126495242ac81085bbaf18f7df2].[sys].[extended_index_581577110_384000].pk0
Expr1006
Nested Loops
(Inner Join)
Cost: 2%
Nested Loops
For each row in the top (outer) input, scan the bottom (inner) input, and output matching rows.
Physical OperationNested Loops
Logical OperationInner Join
Estimated Execution ModeRow
Estimated Operator Cost0.0041798 (2%)
Estimated I/O Cost0
Estimated CPU Cost0.00418
Estimated Subtree Cost0.166405
Estimated Number of Executions1
Estimated Number of Rows1000
Estimated Row Size13 B
Node ID4
Output List
[fiddle_eb4e4126495242ac81085bbaf18f7df2].[sys].[extended_index_581577110_384000].Attr
[fiddle_eb4e4126495242ac81085bbaf18f7df2].[sys].[extended_index_581577110_384000].pk0
Attr
Outer References
Id
LimitId
Table-valued function
[GeodeticTessellation]
Cost: 1%
Table-valued function
Physical OperationTable-valued function
Logical OperationTable-valued function
Estimated Execution ModeRow
Estimated Operator Cost0.0010002 (1%)
Estimated I/O Cost0
Estimated CPU Cost0.0010002
Estimated Subtree Cost0.0010002
Estimated Number of Executions1
Estimated Number of Rows1000
Estimated Row Size18 B
Node ID5
Output List
Id
Attr
LimitId
Object
[GeodeticTessellation]
Clustered Index Seek (Spatial)
[extended_index_581577110_384000].[…
Cost: 83%
Clustered Index Seek (Spatial)
Scanning a particular range of rows from a clustered index.
Physical OperationClustered Index Seek
Logical OperationClustered Index Seek
Estimated Execution ModeRow
StorageRowStore
Estimated Operator Cost0.161225 (83%)
Estimated I/O Cost0.003125
Estimated CPU Cost0.0001581
Estimated Subtree Cost0.161225
Estimated Number of Executions995.377
Estimated Number of Rows to be Read1
Estimated Number of Rows1
Estimated Row Size12 B
OrderedTrue
Node ID6
Output List
[fiddle_eb4e4126495242ac81085bbaf18f7df2].[sys].[extended_index_581577110_384000].Attr
[fiddle_eb4e4126495242ac81085bbaf18f7df2].[sys].[extended_index_581577110_384000].pk0
Object
[fiddle_eb4e4126495242ac81085bbaf18f7df2].[sys].[extended_index_581577110_384000].[IX_TestSpatial]
Seek Predicates
Seek Keys[1]: Start: [fiddle_eb4e4126495242ac81085bbaf18f7df2].[sys].[extended_index_581577110_384000].Id >= Scalar Operator([Id]), End: [fiddle_eb4e4126495242ac81085bbaf18f7df2].[sys].[extended_index_581577110_384000].Id <= Scalar Operator([LimitId])
Clustered Index Seek (Clustered)
[GeoTest].[PK__GeoTest__EC445C5F9CF…
Cost: 2%
Clustered Index Seek (Clustered)
Scanning a particular range of rows from a clustered index.
Physical OperationClustered Index Seek
Logical OperationClustered Index Seek
Estimated Execution ModeRow
StorageRowStore
Estimated Operator Cost0.0032831 (2%)
Estimated I/O Cost0.003125
Estimated CPU Cost0.0001581
Estimated Subtree Cost0.0032831
Estimated Number of Executions1
Estimated Number of Rows to be Read1
Estimated Number of Rows1
Estimated Row Size4035 B
OrderedTrue
Node ID7
Output List
[fiddle_eb4e4126495242ac81085bbaf18f7df2].[dbo].[GeoTest].Coordinates
Object
[fiddle_eb4e4126495242ac81085bbaf18f7df2].[dbo].[GeoTest].[PK__GeoTest__EC445C5F9CFC1291]
Seek Predicates
Seek Keys[1]: Prefix: [fiddle_eb4e4126495242ac81085bbaf18f7df2].[dbo].[GeoTest].GeoId = Scalar Operator([fiddle_eb4e4126495242ac81085bbaf18f7df2].[sys].[extended_index_581577110_384000].[pk0])
Filter
Cost: 1%
Filter
Physical OperationFilter
Logical OperationFilter
Estimated Execution ModeRow
Estimated Operator Cost0.0010014 (1%)
Estimated I/O Cost0
Estimated CPU Cost0.0010014
Estimated Subtree Cost0.0010025
Estimated Number of Executions1
Estimated Number of Rows1
Estimated Row Size9 B
Node ID8
Predicate
CASE WHEN [Expr1003]>(2) THEN SridMatch([fiddle_eb4e4126495242ac81085bbaf18f7df2].[dbo].[GeoTest].[Coordinates],[@Point]) ELSE [fiddle_eb4e4126495242ac81085bbaf18f7df2].[dbo].[GeoTest].[Coordinates].STDistance([@Point])<=(1.0000000000000000e+000) END
Constant Scan
Cost: 0%
Constant Scan
Physical OperationConstant Scan
Logical OperationConstant Scan
Estimated Execution ModeRow
Estimated Operator Cost0.0000012 (0%)
Estimated I/O Cost0
Estimated CPU Cost0.0000012
Estimated Subtree Cost0.0000012
Estimated Number of Executions1
Estimated Number of Rows1
Estimated Row Size9 B
Node ID9
DECLARE @Point GEOGRAPHY = 'Point(40.755656 -73.587627)';

SELECT Coordinates
FROM dbo.GeoTest WITH(INDEX = IX_TestSpatial)
WHERE Coordinates.STDistance(@Point) <= 1;


Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4223.1"><BatchSequence><Batch><Statements><StmtSimple StatementText="DECLARE @Point GEOGRAPHY = &apos;Point(40.755656 -73.587627)&apos;;&#xa;&#xa;" StatementId="1" StatementCompId="1" StatementType="ASSIGN" RetrievedFromCache="false"></StmtSimple><StmtSimple StatementText="SELECT Coordinates&#xa;FROM dbo.GeoTest WITH(INDEX = IX_TestSpatial)&#xa;WHERE Coordinates.STDistance(@Point) &lt;= 1" StatementId="2" StatementCompId="2" StatementType="SELECT" RetrievedFromCache="false" StatementSubTreeCost="0.193235" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x892348EB4401A1B0" QueryPlanHash="0xA92DFD1EC234DFD3" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="150"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="48" CompileTime="0" CompileCPU="0" CompileMemory="320"><MemoryGrantInfo SerialRequiredMemory="1024" SerialDesiredMemory="1040" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1428256"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2025-03-18T12:47:58.69" ModificationCount="0" SamplingPercent="100" Statistics="[IX_TestSpatial]" Table="[extended_index_581577110_384000]" Schema="[sys]" Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]"></StatisticsInfo><StatisticsInfo LastUpdate="2025-03-18T12:47:58.83" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000003_24927208]" Table="[extended_index_581577110_384000]" Schema="[sys]" Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]"></StatisticsInfo><StatisticsInfo LastUpdate="2025-03-18T12:47:58.83" ModificationCount="0" SamplingPercent="100" Statistics="[PK__GeoTest__EC445C5F9CFC1291]" Table="[GeoTest]" Schema="[dbo]" Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><RelOp NodeId="0" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-06" AvgRowSize="4035" EstimatedTotalSubtreeCost="0.193235" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[dbo]" Table="[GeoTest]" Column="Coordinates"></ColumnReference></OutputList><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[dbo]" Table="[GeoTest]" Column="Coordinates"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference></OuterReferences><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-06" AvgRowSize="4036" EstimatedTotalSubtreeCost="0.192228" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[dbo]" Table="[GeoTest]" Column="Coordinates"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference></OutputList><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[sys]" Table="[extended_index_581577110_384000]" Column="pk0"></ColumnReference></OuterReferences><RelOp NodeId="2" PhysicalOp="Hash Match" LogicalOp="Aggregate" EstimateRows="1" EstimateIO="0" EstimateCPU="0.0224355" AvgRowSize="12" EstimatedTotalSubtreeCost="0.188941" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1003"></ColumnReference><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[sys]" Table="[extended_index_581577110_384000]" Column="pk0"></ColumnReference></OutputList><MemoryFractions Input="1" Output="1"></MemoryFractions><Hash><DefinedValues><DefinedValue><ColumnReference Column="Expr1003"></ColumnReference><ScalarOperator ScalarString="MAX([Expr1006])"><Aggregate Distinct="0" AggType="MAX"><ScalarOperator><Identifier><ColumnReference Column="Expr1006"></ColumnReference></Identifier></ScalarOperator></Aggregate></ScalarOperator></DefinedValue></DefinedValues><HashKeysBuild><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[sys]" Table="[extended_index_581577110_384000]" Column="pk0"></ColumnReference></HashKeysBuild><RelOp NodeId="3" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1000" EstimateIO="0" EstimateCPU="0.0001" AvgRowSize="12" EstimatedTotalSubtreeCost="0.166505" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[sys]" Table="[extended_index_581577110_384000]" Column="pk0"></ColumnReference><ColumnReference Column="Expr1006"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1006"></ColumnReference><ScalarOperator ScalarString="[fiddle_eb4e4126495242ac81085bbaf18f7df2].[sys].[extended_index_581577110_384000].[Attr]+[Attr]"><Arithmetic Operation="ADD"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[sys]" Table="[extended_index_581577110_384000]" Column="Attr"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Attr"></ColumnReference></Identifier></ScalarOperator></Arithmetic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="4" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1000" EstimateIO="0" EstimateCPU="0.00418" AvgRowSize="13" EstimatedTotalSubtreeCost="0.166405" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[sys]" Table="[extended_index_581577110_384000]" Column="Attr"></ColumnReference><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[sys]" Table="[extended_index_581577110_384000]" Column="pk0"></ColumnReference><ColumnReference Column="Attr"></ColumnReference></OutputList><NestedLoops Optimized="0"><OuterReferences><ColumnReference Column="Id"></ColumnReference><ColumnReference Column="LimitId"></ColumnReference></OuterReferences><RelOp NodeId="5" PhysicalOp="Table-valued function" LogicalOp="Table-valued function" EstimateRows="1000" EstimateIO="0" EstimateCPU="0.00100016" AvgRowSize="18" EstimatedTotalSubtreeCost="0.00100016" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Id"></ColumnReference><ColumnReference Column="Attr"></ColumnReference><ColumnReference Column="LimitId"></ColumnReference></OutputList><TableValuedFunction><DefinedValues><DefinedValue><ColumnReference Column="Id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Attr"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="LimitId"></ColumnReference></DefinedValue></DefinedValues><Object Table="[GeodeticTessellation]"></Object><ParameterList><ScalarOperator ScalarString="[@Point]"><Identifier><ColumnReference Column="@Point"></ColumnReference></Identifier></ScalarOperator><ScalarOperator ScalarString="(768)"><Const ConstValue="(768)"></Const></ScalarOperator><ScalarOperator ScalarString="(9)"><Const ConstValue="(9)"></Const></ScalarOperator><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator><ScalarOperator ScalarString="(1.0000000000000000e+000)"><Const ConstValue="(1.0000000000000000e+000)"></Const></ScalarOperator></ParameterList></TableValuedFunction></RelOp><RelOp NodeId="6" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="12" EstimatedTotalSubtreeCost="0.161225" TableCardinality="256" Parallel="0" EstimateRebinds="994.377" EstimateRewinds="4.62341" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[sys]" Table="[extended_index_581577110_384000]" Column="Attr"></ColumnReference><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[sys]" Table="[extended_index_581577110_384000]" Column="pk0"></ColumnReference></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[sys]" Table="[extended_index_581577110_384000]" Column="Attr"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[sys]" Table="[extended_index_581577110_384000]" Column="pk0"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[sys]" Table="[extended_index_581577110_384000]" Index="[IX_TestSpatial]" TableReferenceId="-1" IndexKind="Spatial" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><StartRange ScanType="GE"><RangeColumns><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[sys]" Table="[extended_index_581577110_384000]" Column="Id"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[Id]"><Identifier><ColumnReference Column="Id"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></StartRange><EndRange ScanType="LE"><RangeColumns><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[sys]" Table="[extended_index_581577110_384000]" Column="Id"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[LimitId]"><Identifier><ColumnReference Column="LimitId"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></EndRange></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp></ComputeScalar></RelOp></Hash></RelOp><RelOp NodeId="7" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="4035" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="1" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[dbo]" Table="[GeoTest]" Column="Coordinates"></ColumnReference></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[dbo]" Table="[GeoTest]" Column="Coordinates"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[dbo]" Table="[GeoTest]" Index="[PK__GeoTest__EC445C5F9CFC1291]" IndexKind="Clustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[dbo]" Table="[GeoTest]" Column="GeoId"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_eb4e4126495242ac81085bbaf18f7df2].[sys].[extended_index_581577110_384000].[pk0]"><Identifier><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[sys]" Table="[extended_index_581577110_384000]" Column="pk0"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp><RelOp NodeId="8" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="1" EstimateIO="0" EstimateCPU="0.00100138" AvgRowSize="9" EstimatedTotalSubtreeCost="0.00100254" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><Filter StartupExpression="1"><RelOp NodeId="9" PhysicalOp="Constant Scan" LogicalOp="Constant Scan" EstimateRows="1" EstimateIO="0" EstimateCPU="1.157e-06" AvgRowSize="9" EstimatedTotalSubtreeCost="1.157e-06" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><ConstantScan></ConstantScan></RelOp><Predicate><ScalarOperator ScalarString="CASE WHEN [Expr1003]&gt;(2) THEN SridMatch([fiddle_eb4e4126495242ac81085bbaf18f7df2].[dbo].[GeoTest].[Coordinates],[@Point]) ELSE [fiddle_eb4e4126495242ac81085bbaf18f7df2].[dbo].[GeoTest].[Coordinates].STDistance([@Point])&lt;=(1.0000000000000000e+000) END"><IF><Condition><ScalarOperator><Compare CompareOp="GT"><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(2)"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Intrinsic FunctionName="SridMatch"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[dbo]" Table="[GeoTest]" Column="Coordinates"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="@Point"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator></Then><Else><ScalarOperator><Compare CompareOp="LE"><ScalarOperator><UDTMethod><CLRFunction Assembly="Microsoft.SqlServer.Types" Class="Microsoft.SqlServer.Types.SqlGeography" Method="STDistance"></CLRFunction><ScalarOperator><Identifier><ColumnReference Database="[fiddle_eb4e4126495242ac81085bbaf18f7df2]" Schema="[dbo]" Table="[GeoTest]" Column="Coordinates"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="@Point"></ColumnReference></Identifier></ScalarOperator></UDTMethod></ScalarOperator><ScalarOperator><Const ConstValue="(1.0000000000000000e+000)"></Const></ScalarOperator></Compare></ScalarOperator></Else></IF></ScalarOperator></Predicate></Filter></RelOp></NestedLoops></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

DECLARE @Point GEOGRAPHY = 'Point(40.755656 -73.587627)';

ASSIGN
ASSIGN
Estimated Operator Cost0 (0%)
Statement
DECLARE @Point GEOGRAPHY = 'Point(40.755656 -73.587627)';

SELECT Coordinates
FROM dbo.GeoTest WITH(INDEX = IX_TestSpatial)
WHERE Coordinates.STDistance(@Point) <= 1
SELECT
SELECT
Cached plan size48 KB
Estimated Operator Cost0 (0%)
Estimated Subtree Cost0.193235
Estimated Number of Rows1
Statement
SELECT Coordinates
FROM dbo.GeoTest WITH(INDEX = IX_TestSpatial)
WHERE Coordinates.STDistance(@Point) <= 1
Nested Loops
(Inner Join)
Cost: 0%
Nested Loops
For each row in the top (outer) input, scan the bottom (inner) input, and output matching rows.
Physical OperationNested Loops
Logical OperationInner Join
Estimated Execution ModeRow
Estimated Operator Cost0.0000045 (0%)
Estimated I/O Cost0
Estimated CPU Cost0.0000042
Estimated Subtree Cost0.193235
Estimated Number of Executions1
Estimated Number of Rows1
Estimated Row Size4035 B
Node ID0
Output List
[fiddle_eb4e4126495242ac81085bbaf18f7df2].[dbo].[GeoTest].Coordinates
Outer References
[fiddle_eb4e4126495242ac81085bbaf18f7df2].[dbo].[GeoTest].Coordinates
Expr1003
Nested Loops
(Inner Join)
Cost: 0%
Nested Loops
For each row in the top (outer) input, scan the bottom (inner) input, and output matching rows.
Physical OperationNested Loops
Logical OperationInner Join
Estimated Execution ModeRow
Estimated Operator Cost0.0000039 (0%)
Estimated I/O Cost0
Estimated CPU Cost0.0000042
Estimated Subtree Cost0.192228
Estimated Number of Executions1
Estimated Number of Rows1
Estimated Row Size4036 B
Node ID1
Output List
[fiddle_eb4e4126495242ac81085bbaf18f7df2].[dbo].[GeoTest].Coordinates
Expr1003
Outer References
[fiddle_eb4e4126495242ac81085bbaf18f7df2].[sys].[extended_index_581577110_384000].pk0
Hash Match
(Aggregate)
Cost: 12%
Hash Match
Use each row from the top input to build a hash table, and each row from the bottom input to probe into the hash table, outputting all matching rows.
Physical OperationHash Match
Logical OperationAggregate
Estimated Execution ModeRow
Estimated Operator Cost0.022436 (12%)
Estimated I/O Cost0
Estimated CPU Cost0.0224355
Estimated Subtree Cost0.188941
Estimated Number of Executions1
Estimated Number of Rows1
Estimated Row Size12 B
Node ID2
Output List
Expr1003
[fiddle_eb4e4126495242ac81085bbaf18f7df2].[sys].[extended_index_581577110_384000].pk0
Compute Scalar
Cost: 0%
Compute Scalar
Compute new values from existing values in a row.
Physical OperationCompute Scalar
Logical OperationCompute Scalar
Estimated Execution ModeRow
Estimated Operator Cost0.0001 (0%)
Estimated I/O Cost0
Estimated CPU Cost0.0001
Estimated Subtree Cost0.166505
Estimated Number of Executions1
Estimated Number of Rows1000
Estimated Row Size12 B
Node ID3
Output List
[fiddle_eb4e4126495242ac81085bbaf18f7df2].[sys].[extended_index_581577110_384000].pk0
Expr1006
Nested Loops
(Inner Join)
Cost: 2%
Nested Loops
For each row in the top (outer) input, scan the bottom (inner) input, and output matching rows.
Physical OperationNested Loops
Logical OperationInner Join
Estimated Execution ModeRow
Estimated Operator Cost0.0041798 (2%)
Estimated I/O Cost0
Estimated CPU Cost0.00418
Estimated Subtree Cost0.166405
Estimated Number of Executions1
Estimated Number of Rows1000
Estimated Row Size13 B
Node ID4
Output List
[fiddle_eb4e4126495242ac81085bbaf18f7df2].[sys].[extended_index_581577110_384000].Attr
[fiddle_eb4e4126495242ac81085bbaf18f7df2].[sys].[extended_index_581577110_384000].pk0
Attr
Outer References
Id
LimitId
Table-valued function
[GeodeticTessellation]
Cost: 1%
Table-valued function
Physical OperationTable-valued function
Logical OperationTable-valued function
Estimated Execution ModeRow
Estimated Operator Cost0.0010002 (1%)
Estimated I/O Cost0
Estimated CPU Cost0.0010002
Estimated Subtree Cost0.0010002
Estimated Number of Executions1
Estimated Number of Rows1000
Estimated Row Size18 B
Node ID5
Output List
Id
Attr
LimitId
Object
[GeodeticTessellation]
Clustered Index Seek (Spatial)
[extended_index_581577110_384000].[…
Cost: 83%
Clustered Index Seek (Spatial)
Scanning a particular range of rows from a clustered index.
Physical OperationClustered Index Seek
Logical OperationClustered Index Seek
Estimated Execution ModeRow
StorageRowStore
Estimated Operator Cost0.161225 (83%)
Estimated I/O Cost0.003125
Estimated CPU Cost0.0001581
Estimated Subtree Cost0.161225
Estimated Number of Executions995.377
Estimated Number of Rows to be Read1
Estimated Number of Rows1
Estimated Row Size12 B
OrderedTrue
Node ID6
Output List
[fiddle_eb4e4126495242ac81085bbaf18f7df2].[sys].[extended_index_581577110_384000].Attr
[fiddle_eb4e4126495242ac81085bbaf18f7df2].[sys].[extended_index_581577110_384000].pk0
Object
[fiddle_eb4e4126495242ac81085bbaf18f7df2].[sys].[extended_index_581577110_384000].[IX_TestSpatial]
Seek Predicates
Seek Keys[1]: Start: [fiddle_eb4e4126495242ac81085bbaf18f7df2].[sys].[extended_index_581577110_384000].Id >= Scalar Operator([Id]), End: [fiddle_eb4e4126495242ac81085bbaf18f7df2].[sys].[extended_index_581577110_384000].Id <= Scalar Operator([LimitId])
Clustered Index Seek (Clustered)
[GeoTest].[PK__GeoTest__EC445C5F9CF…
Cost: 2%
Clustered Index Seek (Clustered)
Scanning a particular range of rows from a clustered index.
Physical OperationClustered Index Seek
Logical OperationClustered Index Seek
Estimated Execution ModeRow
StorageRowStore
Estimated Operator Cost0.0032831 (2%)
Estimated I/O Cost0.003125
Estimated CPU Cost0.0001581
Estimated Subtree Cost0.0032831
Estimated Number of Executions1
Estimated Number of Rows to be Read1
Estimated Number of Rows1
Estimated Row Size4035 B
OrderedTrue
Node ID7
Output List
[fiddle_eb4e4126495242ac81085bbaf18f7df2].[dbo].[GeoTest].Coordinates
Object
[fiddle_eb4e4126495242ac81085bbaf18f7df2].[dbo].[GeoTest].[PK__GeoTest__EC445C5F9CFC1291]
Seek Predicates
Seek Keys[1]: Prefix: [fiddle_eb4e4126495242ac81085bbaf18f7df2].[dbo].[GeoTest].GeoId = Scalar Operator([fiddle_eb4e4126495242ac81085bbaf18f7df2].[sys].[extended_index_581577110_384000].[pk0])
Filter
Cost: 1%
Filter
Physical OperationFilter
Logical OperationFilter
Estimated Execution ModeRow
Estimated Operator Cost0.0010014 (1%)
Estimated I/O Cost0
Estimated CPU Cost0.0010014
Estimated Subtree Cost0.0010025
Estimated Number of Executions1
Estimated Number of Rows1
Estimated Row Size9 B
Node ID8
Predicate
CASE WHEN [Expr1003]>(2) THEN SridMatch([fiddle_eb4e4126495242ac81085bbaf18f7df2].[dbo].[GeoTest].[Coordinates],[@Point]) ELSE [fiddle_eb4e4126495242ac81085bbaf18f7df2].[dbo].[GeoTest].[Coordinates].STDistance([@Point])<=(1.0000000000000000e+000) END
Constant Scan
Cost: 0%
Constant Scan
Physical OperationConstant Scan
Logical OperationConstant Scan
Estimated Execution ModeRow
Estimated Operator Cost0.0000012 (0%)
Estimated I/O Cost0
Estimated CPU Cost0.0000012
Estimated Subtree Cost0.0000012
Estimated Number of Executions1
Estimated Number of Rows1
Estimated Row Size9 B
Node ID9
Cached plan size	32 KB
Estimated Operator Cost	0 (0%)
Estimated Subtree Cost	0.0042838
Estimated Number of Rows	1
Physical Operation	Filter
Logical Operation	Filter
Estimated Execution Mode	Row
Estimated Operator Cost	0.0010007 (23%)
Estimated I/O Cost	0
Estimated CPU Cost	0.0010007
Estimated Subtree Cost	0.0042838
Estimated Number of Executions	1
Estimated Number of Rows	1
Estimated Row Size	4035 B
Node ID	0
Physical Operation	Clustered Index Scan
Logical Operation	Clustered Index Scan
Estimated Execution Mode	Row
Storage	RowStore
Estimated Operator Cost	0.0032831 (77%)
Estimated I/O Cost	0.003125
Estimated CPU Cost	0.0001581
Estimated Subtree Cost	0.0032831
Estimated Number of Executions	1
Estimated Number of Rows to be Read	1
Estimated Number of Rows	1
Estimated Row Size	4035 B
Ordered	False
Node ID	1
Cached plan size	48 KB
Estimated Operator Cost	0 (0%)
Estimated Subtree Cost	0.193235
Estimated Number of Rows	1
Physical Operation	Nested Loops
Logical Operation	Inner Join
Estimated Execution Mode	Row
Estimated Operator Cost	0.0000045 (0%)
Estimated I/O Cost	0
Estimated CPU Cost	0.0000042
Estimated Subtree Cost	0.193235
Estimated Number of Executions	1
Estimated Number of Rows	1
Estimated Row Size	4035 B
Node ID	0
Physical Operation	Hash Match
Logical Operation	Aggregate
Estimated Execution Mode	Row
Estimated Operator Cost	0.022436 (12%)
Estimated I/O Cost	0
Estimated CPU Cost	0.0224355
Estimated Subtree Cost	0.188941
Estimated Number of Executions	1
Estimated Number of Rows	1
Estimated Row Size	12 B
Node ID	2
Physical Operation	Compute Scalar
Logical Operation	Compute Scalar
Estimated Execution Mode	Row
Estimated Operator Cost	0.0001 (0%)
Estimated I/O Cost	0
Estimated CPU Cost	0.0001
Estimated Subtree Cost	0.166505
Estimated Number of Executions	1
Estimated Number of Rows	1000
Estimated Row Size	12 B
Node ID	3
Physical Operation	Table-valued function
Logical Operation	Table-valued function
Estimated Execution Mode	Row
Estimated Operator Cost	0.0010002 (1%)
Estimated I/O Cost	0
Estimated CPU Cost	0.0010002
Estimated Subtree Cost	0.0010002
Estimated Number of Executions	1
Estimated Number of Rows	1000
Estimated Row Size	18 B
Node ID	5
Physical Operation	Clustered Index Seek
Logical Operation	Clustered Index Seek
Estimated Execution Mode	Row
Storage	RowStore
Estimated Operator Cost	0.161225 (83%)
Estimated I/O Cost	0.003125
Estimated CPU Cost	0.0001581
Estimated Subtree Cost	0.161225
Estimated Number of Executions	995.377
Estimated Number of Rows to be Read	1
Estimated Number of Rows	1
Estimated Row Size	12 B
Ordered	True
Node ID	6
Physical Operation	Constant Scan
Logical Operation	Constant Scan
Estimated Execution Mode	Row
Estimated Operator Cost	0.0000012 (0%)
Estimated I/O Cost	0
Estimated CPU Cost	0.0000012
Estimated Subtree Cost	0.0000012
Estimated Number of Executions	1
Estimated Number of Rows	1
Estimated Row Size	9 B
Node ID	9