Indexes

  • free_mascot

    One Orange Chip

    Points: 27168

    Comments posted to this topic are about the item Indexes

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    Thanks for the question.

    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: 21876

    Remember that "Filtered indexes" are used only if the WHERE clause matches or is compatible with the filter.

    View http://www.sqlservercentral.com/questions/T-SQL/105826/

  • Ed Wagner

    SSC Guru

    Points: 286969

    I love indexing questions. Good way to end the week.

  • Colin Davidson

    Hall of Fame

    Points: 3756

    They can also be used on a view as long as it is a persisted view.

  • Thomas Abraham

    SSChampion

    Points: 10761

    Thanks for the great question! Inspired me to do a little refresher study of indexes (and views too). Got me to working on a couple of needed views for reporting. Can't get too carried away, as the next version of our ERP software is "just around the corner" (where it has been for 6 months) and involves major changes to the DB. But, I digress ...

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • RLilj33

    SSCrazy

    Points: 2153

    Thanks for the question.

    Almost got it wrong when I read this from SQL Authority, Pinal Dave: "They can be used on Views only if they are persisted views."

    - http://blog.sqlauthority.com/2008/09/01/sql-server-2008-introduction-to-filtered-index-improve-performance-with-filtered-index/

    So I was wondering if there were 4 correct answers until I read in BOL, "You cannot create a filtered index on a view."

    - http://technet.microsoft.com/en-us/library/cc280372.aspx

  • PChiragS

    SSCarpal Tunnel

    Points: 4965

    nice question..

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    Ed Wagner (3/21/2014)


    I love indexing questions. Good way to end the week.

    +1

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    Nice question! I love filtered indexes. They are very useful for the data mining algorithms.

    Thanks

    Igor

    Igor Micev,
    My blog: www.igormicev.com

  • Hany Helmy

    SSChampion

    Points: 13436

    Good question, had to do some researches, but I think it should worth more than 1 point 😎

  • TomThomson

    SSC Guru

    Points: 104772

    Colin Davidson (3/21/2014)


    They can also be used on a view as long as it is a persisted view.

    I though that too, so ended up which of the four options was the non-included one. And thus got it wrong. I can't imagine any good reason for not allowing filtered indexes on (indexed) views, but they really aren't allowed.

    Tom

  • free_mascot

    One Orange Chip

    Points: 27168

    Thanks and appreciate all your comments!

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • This was removed by the editor as SPAM

  • Hugo Kornelis

    SSC Guru

    Points: 64675

    I'm sorry, but I do not understand where all the praise is coming from. The answers are simply incorrect. And the explanation merely repeats the answers that the author thinks are correct, and then gives a link to a page that does not support these answers.

    * "These types of indexes do not exist in SQL SERVER 2012". Indeed, this is a false statement. No argument here.

    * "They reduce the index maintenance cost compared to index views". Not true. The link given states that index maintenance for a filtered index is less than that for a full-table nonclustered index (and even that is not necessarily true). There is no comparison made to indexed views. An indexed view that includes only a single row from a million row table will probably have lower maintenance cost than a filtered index that includes half of those rows.

    * "This is one type of Clustered index". Indeed, this is a false statement. No argument here.

    * "This is an optimized nonclustered index". Nope, nothing optimized about it. It is exactly the same as a "normal" nonclustered index, except that it includes less columns. Yes, I did see that these words are an exact copy/paste of Books Online - so that simply means that Books Online is wrong.

    * "You can create a Filtered Index on a view". Indeed, this is a false statement. I though it would be possible, but I had never tried it so I decided to try it - and I got error message 10610, "... Filtered indexes are only supported on tables ..."

    * "These indexes improve the plan quality during query compilation compared to index views". Rubbish. In comparison to full-table nonclustered indexes, a filtered index might, when designed and used correctly, improve plan quality (if it is being used at all - more on that in my next post). A well-designed indexed view may optimizer the plan even more. It all depends on what you are querying. An indexed view can make joined and aggregated data available directly from the index. A filtered index can never do that. The only benefit a filtered index has is that it contains less rows.


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

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