Indexed Views

  • sknox

    SSChampion

    Points: 12294

    Comments posted to this topic are about the item Indexed Views

  • M&M

    SSC-Insane

    Points: 21699

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

    M&M

  • Koen Verbeeck

    SSC Guru

    Points: 258985

    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

  • Carlo Romagnano

    SSC-Insane

    Points: 22015

    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

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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/

  • Carlo Romagnano

    SSC-Insane

    Points: 22015

    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

  • Arthur Olcot

    SSCertifiable

    Points: 6008

    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

  • sknox

    SSChampion

    Points: 12294

    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.

  • Britt Cluff

    SSCertifiable

    Points: 5083

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

    http://brittcluff.blogspot.com/

  • Sathishk

    SSChasing Mays

    Points: 628

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

  • Sathishk

    SSChasing Mays

    Points: 628

    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

  • Sathishk

    SSChasing Mays

    Points: 628

    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

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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 41 total)

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