Hugo Kornelis (2/10/2012)
I hate questions where I have to second-guess the author. Did the author mean that the optimizer would choose to use the indexed view automatically? Or did he mean that you could make the optimizer use it by using hints? I took a 50% chance gamble, and I chose wrong.
BTW, now that I am posting anyway - there is one word in the explanation is wrong: "The query optimizer in lower editions will only consider
the indexed view if you use the NOEXPAND table hint" (emphasis mine) - this is incorrect. With the NOEXPAND hint, the optimizer will not consider the indexed view; this hint forces
the optimizer to use the indexed view. Enterprise edition (and developer edition) is the only place where the optimizer will consider
the indexed view as one of multiple options, and choose the cheapest.
I have to agree. I knew about the NOEXPAND, but reading the question I didn't consider it.
For better assistance in answering your questions, click hereFor tips to get better help with Performance Problems, click hereFor Running Totals and its variations, click here
or when working with partitioned tablesFor more about Tally Tables, click hereFor more about Cross Tabs and Pivots, click here
and hereManaging Transaction Logs
SQL Musings from the Desert Fountain Valley SQL
(My Mirror Blog)