Indexed Views

  • Comments posted to this topic are about the item Indexed Views

  • Thanks for the question. I came across another discussion in this link :

    M&M

  • 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

  • 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.

  • This was removed by the editor as SPAM

  • 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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • 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

  • This was removed by the editor as SPAM

  • 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

  • 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.

  • Learned something new today even though I missed the question. Thanks for submitting.

    http://brittcluff.blogspot.com/

  • Thanks for the question and like the answer options very much 🙂

  • 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

  • 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.

    http://msdn.microsoft.com/en-us/library/ms181151.aspx

  • SathishK (2/10/2012)


    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.

    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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Viewing 15 posts - 1 through 15 (of 40 total)

You must be logged in to reply to this topic. Login to reply