Post reply

Filtered Indexes

  • April 21, 2015 at 11:33 pm

    #317546

    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

  • April 22, 2015 at 12:57 am

    #1792622

    Good question!

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

  • April 22, 2015 at 1:09 am

    #1792623

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

  • April 22, 2015 at 1:21 am

    #1792625

    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!

  • April 22, 2015 at 1:31 am

    #1792627

    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

  • April 22, 2015 at 3:33 am

    #1792651

    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

  • April 22, 2015 at 4:47 am

    #1792663

    Nice question, Thanks!

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

  • April 22, 2015 at 5:22 am

    #1792679

    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.

  • April 22, 2015 at 6:27 am

    #1792696

    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.

  • April 22, 2015 at 6:49 am

    #1792705

    Thanks for the question. Made me think about this.

  • April 22, 2015 at 7:11 am

    #1792720

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

  • April 22, 2015 at 7:26 am

    #1792725

    very good question.

  • April 22, 2015 at 7:26 am

    #1792727

    sestell1 (4/22/2015)

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

    +1

  • April 22, 2015 at 7:27 am

    This was removed by the editor as SPAM

  • April 22, 2015 at 7:31 am

    #1792732

    Great question, thanks for sharing!

    Be still, and know that I am God - Psalm 46:10

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

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