Post reply

Indexes

  • March 20, 2014 at 9:16 pm

    #289618

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

  • March 21, 2014 at 1:05 am

    #1698954

    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

  • March 21, 2014 at 2:32 am

    #1698970

    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/

  • March 21, 2014 at 5:31 am

    #1699036

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

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • March 21, 2014 at 5:34 am

    #1699039

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

  • March 21, 2014 at 7:27 am

    #1699079

    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

  • March 21, 2014 at 7:38 am

    #1699086

    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

  • March 21, 2014 at 8:49 am

    #1699109

    nice question..

  • March 21, 2014 at 9:16 am

    #1699123

    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.

  • March 21, 2014 at 2:26 pm

    #1699239

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

    Thanks

    Igor

    Igor Micev,My blog: www.igormicev.com

  • March 22, 2014 at 5:20 am

    #1699307

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

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • March 23, 2014 at 11:01 am

    #1699359

    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

  • March 23, 2014 at 10:43 pm

    #1699374

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

  • March 24, 2014 at 2:13 am

    This was removed by the editor as SPAM

  • March 30, 2014 at 5:21 pm

    #1701446

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

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