SQL Server不使用将datetime与not NULL无关的索引 [英] SQL Server does not use an index comparing datetime to not null

问题描述

我有一个简单的表与任何其他的无关。它有一个不是PK列，它是一个日期。我已经创建了一个非聚集索引到该列。如果我提出这个查询：

select * from table where datecolumn is not null< - 不使用索引并且变得非常慢。

但是如果我删除不是这样的方式：
select * from table where datecolum is null< - 使用索引并且真的很快。

还有比null更多的null null。

我忘记了一些东西吗？我可以在这里使用过滤索引吗？

提前感谢

解决方案

这是正常现象。它不会使用索引，除非谓词具有足够的选择性来保证它。

这听起来像绝大多数记录不是NULL，而不是通过非集群索引然后不得不执行加载书签查找和随机I / O以检索其余的列以返回它更快更有效地扫描整个聚簇索引。

您可以使用 FORCESEEK 强制您想要的行为。您可能会发现与集群索引扫描相比，所花费的时间和I / O统计数据与屋顶相关。

  SET STATISTICS IO ON 
 
 SELECT * FROM 
 YourTable WITH（FORCESEEK）
 WHERE YourCol IS NOT NULL 
  

I have a simple table not related to any other. It has a not PK column that it is a date. I have created a non-clustered index to that column. If I make this query:

select * from table where datecolumn is not null <-- does not use the index and goes really slow.

But if I remove the not, this way: select * from table where datecolum is null <-- uses the index and goes really fast.

There are much more not nulls than nulls.

Am I forgetting something? Could I use filtered index here?

Thanks in advance.

解决方案

This is normal. It won't use the index unless the predicate is selective enough to warrant it.

It sounds like the vast majority of records are not NULL so instead of finding these via the non clustered index then having to do loads of bookmark lookups and random I/O to retrieve the rest of the columns to return it is quicker and more efficient to just scan the whole clustered index.

You can use FORCESEEK to force the behaviour that you say you want. You will likely find that the time taken and I/O stats go through the roof compared to the clustered index scan.

SET STATISTICS IO ON

SELECT * FROM 
YourTable WITH (FORCESEEK) 
WHERE YourCol IS NOT NULL

这篇关于SQL Server不使用将datetime与not NULL无关的索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！