Filtered Indexes

Question

Post reply

Filtered Indexes

Mark Cowne

One Orange Chip

Points: 26983
More actions
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

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

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

Carlo Romagnano SSC-Insane Points: 22786 More actions · Answer 1

Good question!

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

edwardwill SSCertifiable Points: 5959 More actions · Answer 2

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

Carlo Romagnano SSC-Insane Points: 22786 More actions · Answer 3

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!

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 4

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

Hany Helmy SSChampion Points: 13921 More actions · Answer 5

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

free_mascot One Orange Chip Points: 27164 More actions · Answer 6

Nice question, Thanks!

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

briankwartler Ten Centuries Points: 1332 More actions · Answer 7

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.

alicesql SSCrazy Points: 2649 More actions · Answer 8

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.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 9

akljfhnlaflkj

SSC Guru

Points: 76202

April 22, 2015 at 6:49 am

#1792705

Thanks for the question. Made me think about this.

sestell1 SSChampion Points: 10230 More actions · Answer 10

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

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 11

pmadhavapeddi22

SSCertifiable

Points: 5306

April 22, 2015 at 7:26 am

#1792725

very good question.

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 12

sestell1 (4/22/2015)
Interesting question! I don't use filtered indexes very often, and didn't know filtered indexes had these limitations.

+1

david.gugg SSCertifiable Points: 6172 More actions · Answer 13

Great question, thanks for sharing!

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

alicesql SSCrazy Points: 2649 More actions · Answer 14

Stewart "Arturius" Campbell (4/22/2015)
Good question, thanks Mark
alicesql (4/22/2015)
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.
Business would usually give an indication of what actual data might be expected within a given field. the relevant filters would be created basd on what queries would be executed against said table and what filters would be applied.
for a table devoid of data, there would not be any NULL values, just an empty data set.

I only saw examples online of creating filtered indices on populated tables. So, I'm not sure how an index can be created based on the value of a column that is empty. How can the conditional be satisfied? Is the index defined based on the potential values of Col, but not really 'built' until rows are inserted?