问题描述
我在 SQL Server 2008 上看到了一些奇怪的性能问题，其中包含带有空间索引的可为空的地理列.每个空值都存储为空间索引中的根节点.
I'm seeing some strange performance issues on SQL Server 2008 with a nullable geography column with a spatial index. Each null value is stored as a root node within the spatial index.
例如一个包含 5 000 000 个地址的表，其中 4 000 000 个存储了坐标.
每次查询索引时，我都必须扫描每个根节点，这意味着我必须扫描 1 000 001 个 0 级节点.(所有有效坐标的 1 个根节点 + 1M 个空值)
E.g. A table with 5 000 000 addresses where 4 000 000 has a coordinate stored.
Every time I query the index I have to scan through every root node, meaning I have to scan through 1 000 001 level 0 nodes. (1 root node for all the valid coordinates + 1M nulls)
我找不到文档中提到的这一点，而且我不明白为什么如果索引无法处理它，SQL 会允许此列为空.
I cannot find this mentioned in the documentation, and I cannot see why SQL allows this column to be nullable if the indexing is unable to handle it.
目前，我通过仅将现有坐标存储在单独的表中来绕过此问题，但我想知道这里的最佳做法是什么?
For now I have bypassed this by storing only the existing coordinates in a separate table, but I would like to know what is the best practice here?
(案例已关闭)
我在 sql spatial msdn 论坛上得到了一些帮助，并且有一篇关于这个问题的博客文章:http://www.sqlskills.com/BLOGS/BOBB/post/Be-careful-with-EMPTYNULL-values-and-spatial-indexes.aspxMSDN 文档也确实提到了这一点，但是以一种非常狡猾的方式.
(case closed)
I got some help on the sql spatial msdn forum, and there is a blog post about this issue:http://www.sqlskills.com/BLOGS/BOBB/post/Be-careful-with-EMPTYNULL-values-and-spatial-indexes.aspxAlso the MSDN documentation does infact mention this, but in a very sneaky manner.
NULL 和空实例都计算在内在 0 级，但不会影响表现.0 级将有尽可能多的单元格为 NULL 和空实例在基表.对于地理索引，级别 0 将有尽可能多的单元格NULL 和空实例 +1 单元格，因为查询样本被算作1
文本中没有任何地方承诺空值不会影响地理位置的性能.只有几何体不受影响.
Nowhere in the text is it promised that nulls does not affect performance for geography.Only geometry is supposed to be unaffected.
推荐答案
只是一个后续注意事项 - 此问题已在 Sql Server Denali 中修复，并使用新的 AUTO_GRID 索引(现在是默认值).NULL 值将不再填充在根索引节点中.
Just a follow-up note - this issue has been fixed in Sql Server Denali with the new AUTO_GRID indexes (which are now the default). NULL values will no longer be populated in the root index node.
这篇关于SQL Server 2008 在具有空间索引的可空地理列上的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！