February 9, 2012 at 8:01 pm
Comments posted to this topic are about the item Indexed Views
February 10, 2012 at 12:17 am
Nice question. I vaguely remembered reading something about this in my certification prep, it's not something I encounter daily.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 10, 2012 at 1:25 am
I know that query optimizer use index on view only for some edition, but with noexpand you can force the use of index. I still working with sql2000. In sql2008 r2, I think it would be better to use filtered index.
February 10, 2012 at 1:49 am
This was removed by the editor as SPAM
February 10, 2012 at 2:32 am
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.
February 10, 2012 at 2:47 am
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.
+1
February 10, 2012 at 2:51 am
This was removed by the editor as SPAM
February 10, 2012 at 5:14 am
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.
+1
February 10, 2012 at 5:46 am
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.
February 10, 2012 at 6:22 am
Learned something new today even though I missed the question. Thanks for submitting.
http://brittcluff.blogspot.com/
February 10, 2012 at 8:16 am
Thanks for the question and like the answer options very much 🙂
February 10, 2012 at 8:22 am
sknox (2/10/2012)
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. 😛
+1
February 10, 2012 at 8:28 am
sknox (2/10/2012)
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.
Using NOEXPAND hint, says the optimizer to consider the index and it won't force its usage. To force a particular index usage, the optional INDEX() clause need to be used.
February 10, 2012 at 8:37 am
SathishK (2/10/2012)
http://msdn.microsoft.com/en-us/library/ms181151.aspx%5B/quote%5D
Using NOEXPAND means that the optimizer may not use any index on the underlying base table(s). The only freedom the optimizer has is to choose which of the indexes on the view to use. And I have never seen a real-world case where there was more than a single index on a view.
Viewing 15 posts - 1 through 15 (of 40 total)
You must be logged in to reply to this topic. Login to reply