Search code examples
sql-serverindexingnon-clustered-indexfiltered-index

Filtered index vs normal non clustered index

I have 270 million records in a table. Currently I have a non clustered index in it on a date column. 99% of time I use rows with date > 1/1/2008..that means 140 million out of it. I am using SQL server 2008.In this situation will it be beneficial to use filtered index other than normal non-clustered index ?

Also if I use "date" datatype instead of "datetime" how beneficial it is ?

Thanks in advance !

Solution

  • Yes, the filtered non clustered index will be used for:

    • queries than scan very very few records, eg. have WHERE date ='20101016' (filter out a single day, few records from the 270M).
    • queries than scan larger date ranges, but touch only the date field: SELECT COUNT(date) FROM ... WHERE date BETWEEN '20080101' AND '20090101'

    And that's about it. Any more complex query will not use the non-clustered index, filtered or not-filtered, because it will hit the index tipping point.

    So in conclusion, for general queries on this table that have a WHERE date > '200080101' clause your proposed filtered non-clustered index will help... nothing. Furthermore, even if you would move the date as the clustered index leftmost key (which is the typical organization of time range queries time series, like your table seems to be, and you should consider it yourself), filtering out 'only' 140M out of 270M is hardly an optimization.

    Proper indexing is a complex topic. I urge you to read everything in this MSDN link, including all the sub-chapters: Designing Indexes. This will give you the bare minimal knowledge to understand some more complicated articles and to be able to discern right from baloney on the plethora of misinformation available out there.