特定列的最佳索引经常被过滤为NULL?-BestindexforparticularcolumnoftenfilteredwithISNULL?

2  

As you have said almost all the queries will have this clause in them. I would suggest to add a BIT column to your table, once a row is deleted (Soft deletion) set it to 1. Also keep this Datetime column probably as Deleted_Datetime to keep track of when records are being deleted.

正如您所说，几乎所有查询都将包含此子句。我建议将位列添加到表中，一旦删除一行(软删除)，将其设置为1。还可以将这个Datetime列保留为Deleted_Datetime，以跟踪记录被删除的时间。

Then Create a Filtered Index on that BIT field something like

然后在那个位域上创建一个过滤索引

CREATE NONCLUSTERED INDEX NCIX_Deleted_Data
ON dbo.Table(Deleted)
WHERE Deleted = 0

Bit being the smallest datatype, and having a filtered index on it will give you a massive performance boost.

位是最小的数据类型，并且有一个经过过滤的索引，这将极大地提高性能。

You can create this filtered index on your Datetime column as well. But the logic is in that case sql server will have to go through 8 bytes of data on index to see if row is filtered or not.

您也可以在Datetime列上创建这个经过过滤的索引。但是逻辑是，在这种情况下，sql server必须在索引上遍历8字节的数据，以查看是否过滤了行。

Whereas if you have Bit column sql server will have to go through 1 byte of data to filter it out or to keep it.

然而，如果您有位列sql server，则必须通过一个字节的数据来过滤或保留它。

Smaller data quick processing, Bigger data more time to process it :).

更小的数据快速处理，更大的数据处理时间:)。

2  

This is a lost battle. You can add a filter to all other indexes, see filtered indexes. But you won't be able to filter the clustered index.

这是一场失败的战斗。您可以向所有其他索引添加筛选器，请参阅过滤索引。但是您无法过滤聚集索引。

A filter on Deleted column will be too unselective for NULLs and all queries would hit the index tipping point. And a bit Deleted column is worse, with a 0/1 selectivity it will be always ignored.

删除列上的过滤器对于空值来说太没有选择性了，所有查询都将达到索引临界点。而一个删除的列更糟糕，因为它将总是被忽略。

I would recommend you investigate partitioned views instead. You can store current data in one table, deleted data in another one, and operate over a view that unions the two. When properly designed (this is why reading the link is critical, to understand the 'proper' part) this scheme works fine and you never scan the 'cold' data.

我建议您调查分区视图。您可以将当前数据存储在一个表中，并在另一个表中删除数据，并在一个视图中操作。如果设计得当(这就是为什么阅读链接对于理解“合适”部分非常重要)，这个方案就会运行得很好，而且你永远不会扫描“冷”数据。