位列索引 [英] Index on a bit column

问题描述

考虑一个带有一个位列的表，该列指示对象是活动的还是非活动的，而大多数项目处于非活动状态(关闭).

Consider a table with a bit column indicating whether the object is active or inactive where the majority of the items are inactive (closed).

我的理解是，由于该列(2)的不同值的数量有限，因此SQL引擎发现执行表扫描以查找未清项目比尝试对位列进行索引更为有效.

My understanding was that because of the limited number of distinct values for this column (2), the SQL Engine found it more efficient to perform a table scan to find the open items rather than attempt to index over a bit column.

SQL 2008具有一项新功能，该功能允许对索引进行筛选.在不了解索引内部的情况下，我将假定索引仅在满足过滤条件时才包含对记录的引用，并且该方法将提供一种检索所有活动记录的有效方法，而不必诉诸于拆分活动记录到单独的表或分区中.

SQL 2008 has a new feature that allows filters on an index. Without know much about the internals of it, I would assume that the index contains a reference to a record only if it meets the filter criteria and that this approach would provide an efficient means of retrieving all of the active records without having to resort to splitting the active records into separate tables or partitions.

我曾经将打开的记录记录的主键放到一个表中，该表用于标识活动记录，然后将主表连接到该活动列表"表中以仅返回活动记录.

I used to place the primary key of the open records records into a table that I used to identify the active records and then joining the main table to this "active list" table to return only the active records.

在这种情况下，出于某种原因，为什么不适合使用过滤索引?

Is there any reason why using a filtered index for this purpose would not be appropriate in this situation?

推荐答案

在Active位字段上过滤的索引是有效的选择.

A filtered index on an Active bit field is a valid choice.

您可能需要为添加特定的更新统计信息 过滤后的索引(尤其是易失性数据) 使用FULLSCAN更新其统计信息.好消息是他们 可能是较小的索引(因此更容易/成本更低) 统计信息进行更新)

You will probably want to add a specific UPDATE STATISTICS for filtered indexes (especially on volatile data) that specifically updates their stats with a FULLSCAN. The good news is that they are likely to be smaller indexes (and therefore easier/less-costly statistics to update)

这是因为更新统计信息阈值是基于基础列而不是仅基于过滤的索引值.

This is because the update statistics threshold is based on the underlying column rather than the filtered index values only.

参考.

这篇关于位列索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！