开发者

SQL Index Filters - not indexing old data

If I have a table with 500m rows dating back to 2007 and everything before 2009 is rarely accessed (historical data), is it wise to apply filters to indexes to only index data after 2009?

From what I see

  1. Indexes are going to be physically smaller
  2. Indexes will perform better for frequently accessed data
  3. Retrieval for older data will be slower
  4. If a sliding date is required, indexes will need to be altered (adjusting it to only show last 2 years, once a year)

What am I missing? What are the negative side affects?

@Ryk made me think of another issue. We are planning to partition the table(s). should filters be applied before partitioning or开发者_JAVA技巧 after?


Technet: Filtered Index Design Guidelines

There are really no downsides when compared to having no index at all.

The upsides are all the upsides you get by having an index on the recent data, and the downsides are the same for having an index (except it's a percentage of the full downside), such as maintenance (insert/update) cost, storage cost etc.


Have you considered partitioning your tables?

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜