>>>

>>>SELECT * FROM  view_expandedcoindrop WHERE MONTH(dropdate) = @rptmonth AND YEAR(dropdate) = @rptyear ORDER BY store,dropdate

>
>Invoice table a couple of million rows.
>The query typically returns a couple of dozen rows.
>I do execution plans on an as-needed basis.
>This query zipped right through.
>If I reduced the execution time of this query by a factor of 1 million, the user would never know the difference.
>

Sorry, but there's information you should be aware of.

Even if you have an index on the dropdate, SQL Server won't use it when running that query. Your execution plan, in all likelihood, will include a scan on the entire table. When you use date functions like MONTH and YEAR, SQL Server needs to evaluate every row, regardless of whether you have an index.

When you go to the library to look for a book, do you start on the very first shelf and manually eye-ball every book number, and see if it matches the book number you're looking for, and not stop until you exhaust every shelf? Because that's what you're asking SQL Server to do. Just because it CAN do it quickly on your table, doesn't mean you should be doing it.

You might want to do a little research into what is SARG-able and non-SARG-able. Look at your execution plan. And then look at the link that Naomi provided.

Yes, your query might well run OK right now. It might or might not run well in the future. Regardless, this is a bad practice.