We came across a scenario which falls into that rare category where additional indexes actually may be beneficial in the Anchor model. Here data is loaded every five minutes into a Data Warehouse, with one anchor of "transactional" nature having more than 100 million rows. We expect almost all data in a given five minute period to be new, but there may be some that have been seen before, but in that case it will still have happened quite recently.
Looking up if data to be loaded is new or known was starting to take time, given that the attribute storing the timestamp of the transaction was scanned in its entirety during the lookup. Here filtered indexes can come to the rescue. The problem with a regular index on an attribute table is that it will become relatively large compared to the narrow attribute table itself, so the benefit of having it is reduced. It will also slow down inserts, so in a scenario like this, where data is loaded often, it's actually significantly slowing down performance rather than to increase it.
A filtered index, however, only indexes a (small) portion of the data. In this scenario having a filtered index of the last 24 hours of transactions is sufficient, by adding it to the attribute table. In SQL Server you cannot have a filtered index that acts like a sliding window, so it has to be recreated, which here is done twice a day using dynamic SQL. Insert performance is impacted very little, thanks to the index being so small, but lookups within the range of the index are now almost instant. A huge benefit for accessing that "hot stuff":
