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. 🙁
If I'd meant automatically, I'd have said automatically. 😛
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.
Yes, that is incorrect, but I think you've picked the wrong word(s) to correct. The intended meaning was "The query optimizer in lower editions will only consider the indexes on the view if you use the NOEXPAND table hint." The reason I used the word consider is that NOEXPAND does not force the use of a particular index, it only tells SQL server not to refer back to the original table.