Aaron Bertrand only needs recent data:
I recently resolved an issue where a query pulling data from the last 30 days would time out due to the table’s size and the lack of a supporting index. Creating a supporting index is possible, but not ideal; it will be very large and may not be useful for most queries and use cases. I wonder how I could implement a filtered index that follows time and is always limited to the last
ndays.
Read on to see how Aaron did this. It’s a clever solution and Aaron does make clear the tricky part of filtered indexes: explicit conditions.