During a talk about spatial indexes and performance last week, I was surprised by a question about using ANSI-92 JOIN - ON syntax in spatial queries. I'd coded:
SELECT * FROM geonames.dbo.geonames g
JOIN Sample_USA.dbo.Counties c
ON g.GEOG.STIntersects(c.geog) = 1
WHERE c.ID = 1569
Later I learned that the question was prompted by the following verbiage from the SQL Server Books Online: "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 geometry1 . method_name ( geometry2 ) comparison_operator valid_number".
There phrase "within the WHERE clause" is a bit too restrictive. The query above using JOIN has the exact same plan as this equivalent query using WHERE does:
SELECT * FROM geonames.dbo.geonames g, Sample_USA.dbo.Counties c
WHERE c.ID = 1569
AND g.GEOG.STIntersects(c.geog) = 1
Works just fine, either form uses the spatial index. The reason why folks would be concerned about the EXACT WORDING rather than the spirit of the advice, is that the second part of the BOL sentence IS true; the spatial method must be BEFORE the comparison predicate. That is:
-- This uses the spatial index
SELECT * FROM geonames.dbo.geonames g, Sample_USA.dbo.Counties c
WHERE c.ID = 1569
AND g.GEOG.STIntersects(c.geog) = 1
-- This doesn't use the spatial index
SELECT * FROM geonames.dbo.geonames g, Sample_USA.dbo.Counties c
WHERE c.ID = 1569
AND 1 = g.GEOG.STIntersects(c.geog)
But go ahead and use the JOIN verb, it uses the spatial index just fine. As a matter of fact, if you want parameter sniffing and query plan reuse it should be:
CREATE PROCEDURE GetNamesForCounty (@county_id int)
AS
SELECT * FROM geonames.dbo.geonames g
JOIN Sample_USA.dbo.Counties c
ON g.GEOG.STIntersects(c.geog) = 1
WHERE c.ID = @county_id
Or call the parameterized query from a database API directly (ie use query parameters rather than variables with parameterized queries). And OF COURSE you usually don't need SELECT * either...