{"id":638,"date":"2009-07-20T13:51:00","date_gmt":"2009-07-20T13:51:00","guid":{"rendered":"\/blogs\/bobb\/post\/Spatial-Indexes-and-ANSI-JOIN-ON-syntax.aspx"},"modified":"2014-01-20T12:16:51","modified_gmt":"2014-01-20T20:16:51","slug":"spatial-indexes-and-ansi-join-on-syntax","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/spatial-indexes-and-ansi-join-on-syntax\/","title":{"rendered":"Spatial Indexes and ANSI JOIN &#8211; ON syntax"},"content":{"rendered":"<p>\nDuring a talk about spatial indexes and performance last week, I was surprised by a question about using ANSI-92 JOIN &#8211; ON syntax in spatial queries. I&#39;d coded:\n<\/p>\n<p>\nSELECT * FROM geonames.dbo.geonames g<br \/>\nJOIN Sample_USA.dbo.Counties c <br \/>\n&nbsp; ON g.GEOG.STIntersects(c.geog) = 1<br \/>\nWHERE c.ID = 1569\n<\/p>\n<p>\nLater I learned that the question was prompted by the following verbiage from the <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/bb895373.aspx\">SQL Server Books Online<\/a>: &quot;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&quot;.\n<\/p>\n<p>\nThere phrase &quot;within the WHERE clause&quot; is a bit too restrictive. The query above using JOIN has the exact same plan as this equivalent query using WHERE&nbsp;does:\n<\/p>\n<p>\nSELECT * FROM geonames.dbo.geonames g, Sample_USA.dbo.Counties c <br \/>\nWHERE c.ID = 1569 <br \/>\nAND g.GEOG.STIntersects(c.geog) = 1\n<\/p>\n<p>\nWorks just fine, either form uses the spatial index. The reason why folks would be concerned about the EXACT WORDING rather than the&nbsp;spirit of the advice,&nbsp;is that the second part of the BOL sentence IS true; the spatial method must be&nbsp;BEFORE the comparison predicate. That is:\n<\/p>\n<p>\n&#8212; This uses the spatial index<br \/>\nSELECT * FROM geonames.dbo.geonames g, Sample_USA.dbo.Counties c <br \/>\nWHERE c.ID = 1569 <br \/>\nAND g.GEOG.STIntersects(c.geog) = 1\n<\/p>\n<p>\n&#8212; This doesn&#39;t use the spatial index<br \/>\nSELECT * FROM geonames.dbo.geonames g, Sample_USA.dbo.Counties c <br \/>\nWHERE c.ID = 1569 <br \/>\nAND 1 = g.GEOG.STIntersects(c.geog)\n<\/p>\n<p>\nBut 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:\n<\/p>\n<p>\nCREATE PROCEDURE GetNamesForCounty (@county_id int)<br \/>\nAS<br \/>\nSELECT * FROM geonames.dbo.geonames g<br \/>\nJOIN Sample_USA.dbo.Counties c <br \/>\n&nbsp; ON g.GEOG.STIntersects(c.geog) = 1<br \/>\nWHERE c.ID = @county_id\n<\/p>\n<p>\nOr 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&#39;t need SELECT * either&#8230;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>During a talk about spatial indexes and performance last week, I was surprised by a question about using ANSI-92 JOIN &#8211; ON syntax in spatial queries. I&#39;d coded: SELECT * FROM geonames.dbo.geonames g JOIN Sample_USA.dbo.Counties c &nbsp; ON g.GEOG.STIntersects(c.geog) = 1 WHERE c.ID = 1569 Later I learned that the question was prompted by the [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[19,36],"tags":[],"class_list":["post-638","post","type-post","status-publish","format-standard","hentry","category-performance","category-sql-server-spatial"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Spatial Indexes and ANSI JOIN - ON syntax - Bob Beauchemin<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/spatial-indexes-and-ansi-join-on-syntax\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Spatial Indexes and ANSI JOIN - ON syntax - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"During a talk about spatial indexes and performance last week, I was surprised by a question about using ANSI-92 JOIN &#8211; ON syntax in spatial queries. I&#039;d coded: SELECT * FROM geonames.dbo.geonames g JOIN Sample_USA.dbo.Counties c &nbsp; ON g.GEOG.STIntersects(c.geog) = 1 WHERE c.ID = 1569 Later I learned that the question was prompted by the [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/spatial-indexes-and-ansi-join-on-syntax\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2009-07-20T13:51:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-01-20T20:16:51+00:00\" \/>\n<meta name=\"author\" content=\"Bob Beauchemin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Bob Beauchemin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/spatial-indexes-and-ansi-join-on-syntax\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/spatial-indexes-and-ansi-join-on-syntax\/\",\"name\":\"Spatial Indexes and ANSI JOIN - ON syntax - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2009-07-20T13:51:00+00:00\",\"dateModified\":\"2014-01-20T20:16:51+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/spatial-indexes-and-ansi-join-on-syntax\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/spatial-indexes-and-ansi-join-on-syntax\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/spatial-indexes-and-ansi-join-on-syntax\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Performance\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/performance\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Spatial Indexes and ANSI JOIN &#8211; ON syntax\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\",\"name\":\"Bob Beauchemin\",\"description\":\"SQL Server Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\",\"name\":\"Bob Beauchemin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"caption\":\"Bob Beauchemin\"},\"sameAs\":[\"http:\/www.sqlskills.com\/blogs\/bobb\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Spatial Indexes and ANSI JOIN - ON syntax - Bob Beauchemin","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/bobb\/spatial-indexes-and-ansi-join-on-syntax\/","og_locale":"en_US","og_type":"article","og_title":"Spatial Indexes and ANSI JOIN - ON syntax - Bob Beauchemin","og_description":"During a talk about spatial indexes and performance last week, I was surprised by a question about using ANSI-92 JOIN &#8211; ON syntax in spatial queries. I&#39;d coded: SELECT * FROM geonames.dbo.geonames g JOIN Sample_USA.dbo.Counties c &nbsp; ON g.GEOG.STIntersects(c.geog) = 1 WHERE c.ID = 1569 Later I learned that the question was prompted by the [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/spatial-indexes-and-ansi-join-on-syntax\/","og_site_name":"Bob Beauchemin","article_published_time":"2009-07-20T13:51:00+00:00","article_modified_time":"2014-01-20T20:16:51+00:00","author":"Bob Beauchemin","twitter_misc":{"Written by":"Bob Beauchemin","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/spatial-indexes-and-ansi-join-on-syntax\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/spatial-indexes-and-ansi-join-on-syntax\/","name":"Spatial Indexes and ANSI JOIN - ON syntax - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2009-07-20T13:51:00+00:00","dateModified":"2014-01-20T20:16:51+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/spatial-indexes-and-ansi-join-on-syntax\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/spatial-indexes-and-ansi-join-on-syntax\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/spatial-indexes-and-ansi-join-on-syntax\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/"},{"@type":"ListItem","position":2,"name":"Performance","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/performance\/"},{"@type":"ListItem","position":3,"name":"Spatial Indexes and ANSI JOIN &#8211; ON syntax"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/","name":"Bob Beauchemin","description":"SQL Server Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e","name":"Bob Beauchemin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","caption":"Bob Beauchemin"},"sameAs":["http:\/www.sqlskills.com\/blogs\/bobb\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/638","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/comments?post=638"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/638\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=638"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=638"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=638"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}