Lynn Pettis (4/17/2014)
ScottPletcher (4/17/2014)
You need to do further research before you change the existing index to a filtered index, because that index will no longer satisfy queries originally using it if you filter out the values it was using.Besides, I was hoping that the date range itself would eliminate enough rows to improve I/O significantly without impairing any existing uses of the index.
What date range? The query wants EVERYTHING prior to a specific date.
That's still a date range, from beginning date to the specified date. As I noted earlier, specific row counts would be necessary to do a full analysis, but coming in by date at least guarantees reducing the total number of rows that need searched.
Isn't if a far greater concern that willy-nilly changing an existing index to a filtered index could destroy the performance of a large number of existing queries that currently perform quite well?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.