Filtered Indexes

  • Comments posted to this topic are about the item Filtered Indexes

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • Good question!

    I do not like "Filtered indexes", they are too much limited. I prefer indexed view.

  • I copied and pasted the code into SQL Server 2014 and got this:

    Msg 156, Level 15, State 1, Line 21

    Incorrect syntax near the keyword 'OR'.

    Msg 156, Level 15, State 1, Line 24

    Incorrect syntax near the keyword 'BETWEEN'.

    Msg 102, Level 15, State 1, Line 30

    Incorrect syntax near 'NOT'.

  • edwardwill (4/22/2015)


    I copied and pasted the code into SQL Server 2014 and got this:

    Msg 156, Level 15, State 1, Line 21

    Incorrect syntax near the keyword 'OR'.

    Msg 156, Level 15, State 1, Line 24

    Incorrect syntax near the keyword 'BETWEEN'.

    Msg 102, Level 15, State 1, Line 30

    Incorrect syntax near 'NOT'.

    That means three indexes out 10 aren't created!

  • Nice question, but a bit of a limited explanation.

    This article has more info:

    What You Can (and Can’t) Do With Filtered Indexes[/url]

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Realy don`t understand why can`t we use "Not In" for a filtered index while we can use "In" operator?! :w00t:

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • Nice question, Thanks!

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

  • Very nice question. I especially like how you included IX_Filtered5 (WHERE Col IN (1,2)) as a workaround for IX_Filtered6 (WHERE Col = 1 OR Col = 2), IX_Filtered8 (WHERE Col >= 1 AND Col <= 2) as a workaround for IX_Filtered7 (Col BETWEEN 1 AND 2), and IX_Filtered10 (WHERE Col <> 1 AND Col <> 2) as a workaround for IX_Filtered9 (WHERE Col NOT IN (1,2)). Well done.

  • I need some help understanding this. When a table is created, the structure is defined. But no data exists in the table until a row is inserted, correct? Without specifying default values for Col, what assumptions can be made about the value contained in Col? I would have expected it to be NULL. What am I missing here? Thanks.

  • Thanks for the question. Made me think about this.

  • Interesting question! I don't use filtered indexes very often, and didn't know filtered indexes had these limitations.

  • very good question.

  • sestell1 (4/22/2015)


    Interesting question! I don't use filtered indexes very often, and didn't know filtered indexes had these limitations.

    +1

  • This was removed by the editor as SPAM

  • Great question, thanks for sharing!


    [font="Tahoma"]Personal blog relating fishing to database administration:[/font]

    [font="Comic Sans MS"]https://davegugg.wordpress.com[/url]/[/font]

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

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