The optimizer should be able to understand the invertability of built-in intrinsics on dates for the purposes of index sargability
3/23/2011 4:32:22 PM
Almost every client I go to I find queries that group by year and month to aggregate date. Even though there is an index on the appropriate date column the index isn't used by the query optimiser to efficiently build the results.
Whats more if this is done in a view/derived table and then filtered by year or year or month again the index isn't used to filter the data. This results in index scans with no seeks.
Often people do these types of queries in CTEs that is referenced more than once in the main query. This results in a large number of index scans, and sorts/hash aggregates.
Create view summaryView
Select year(datecol) yr, month(datecol) mnth, count(1)
Group by year(datecol), month(datecol)
from summaryView where yr = 2001