I guess when the developers decide it is not. Consider the following query:
drop table dbo.shapeParts
go
create table dbo.shapeParts(
shapePartID tinyint not null identity(1,1) primary key,
shapeID tinyint not null,
shapeQuad tinyint null,
shape geometry not null);
go
insert into dbo.shapeParts values
(1,0,geometry::STGeomFromText('POLYGON((0 1,1 1,1 2,0 2,0 1))',1));
insert into dbo.shapeParts values
(1,1,geometry::STGeomFromText('POLYGON((1 1,2 1,2 2,1 2,1 1))',1));
insert into dbo.shapeParts values
(1,2,geometry::STGeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))',1));
insert into dbo.shapeParts values
(1,3,geometry::STGeomFromText('POLYGON((1 0,2 0,2 1,1 1,1 0))',1));
go
create spatial index shapeindex on dbo.shapeParts(shape)
using geometry_grid with (
bounding_box =(0, 0, 2, 2),
grids =(level_1 = low,level_2 = low,level_3 = low,level_4 = low),
cells_per_object = 1)
go
declare @g2 geometry = geometry::STGeomFromText('POINT(1 1)',1);
select top(1) *
from dbo.shapeParts with(index(shapeIndex))
where (shape.STContains(@g2)=1)
or (shape.STIntersects(@g2))=1
goThe Books Online topic "Geography Methods Supported by Spatial Indexes" reads:
Under certain conditions, spatial indexes support the following set-oriented geography methods: STIntersects(), STEquals(), and STDistance(). To be supported by a spatial index, these methods must be used within the WHERE clause of a query, and they must occur within a predicate of the following general form: geography1 . method_name ( geography2 ) comparison_operator valid_number To return a non-null result, geography1 and geography2 must have the same Spatial Reference Identifier (SRID). Otherwise, the method returns NULL.
. What's in question here is what does "under certain conditions" means. The seems simple enough and for other data types, the type of operation preformed with multiple filter predicates is not an issue, but if you actually try to execute this query in RC0, you get an execution time error:
Msg 8635, Level 16, State 10, Line 2
The query processor could not produce a query plan for a query with a spatial index hint. Reason: Could not find required comparison predicate. Try removing the index hints or removing SET FORCEPLAN.
This seemed like a bug to me, so I filed it on connect (see
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=357578). The response?
"Thank you for the bug submission. Spatial indexes match only WHERE/ON clauses made of single atomic predicates or ANDs thereof -- not ORs. We also do not infer that STIntersects here is redundant."
The response that came back was authoritative and well-reasoned. Yet it also smacks of "its not an error because we say it isn't." Hopefully this response (and kind of response) will be reconsidered in the future.