Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase «««12345

Indexed Views Expand / Collapse
Posted Thursday, April 12, 2012 9:08 PM



Group: General Forum Members
Last Login: Today @ 10:37 AM
Points: 23,522, Visits: 37,756
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.

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1282873
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse