jascunce (2/13/2012)

---

Hugo Kornelis (2/13/2012)

---

jascunce (2/13/2012)

---

Revenant (2/13/2012)

---

I ran your code under R2 DataCenter, up to date, and got 'Warning: Index hints supplied for view 'dbo.v1' will be ignored.' 5 times.

The same results regarding the warning message were returned from SQL 2008 R2 instances, Std edition, RTM as well as sp1.

Thanks, Revenant and Jascunce! That confirms my suspicion.

Did you also check the actual execution plan to confirm if the query optimizer used (an index on) the base table or (an index on) the view?

This is interesting. The actual execution plan from the Std instance with sp1 reports that the clustered index from the base table, not the view, was used in each of the 5 queries. Sorry I couldn't get that info from the RTM instance since it was just upgraded but I would think the results would still be the same :-).

I compared that with the execution of the code in a SQL 2008 R2 Enterprise instance with sp1 and the results were like you had reported from the Developer edition; the clustered index from the view was used.