CheeseheadDBA (5/29/2013)
Your original suggestion works great when the parameter passed in returns no results - which was my original question that you've successfully answered. I was just providing more information for the sake of discssuion. If I recompile the stored procedure and run it with a parameter that returns data, it still uses the clustered index scan regardless of whether or not the nonclustered index is created and the clustered index scan actually performs better in this case. Perhaps this is because of the MAX function in the select?
Possibly, would need to see the actual execution plan to see what it is doing to confirm.