April 22, 2015 at 7:32 am
Stewart "Arturius" Campbell (4/22/2015)
Good question, thanks Markalicesql (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?
April 22, 2015 at 7:34 am
alicesql (4/22/2015)
Stewart "Arturius" Campbell (4/22/2015)
Good question, thanks Markalicesql (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?
It's just an index. If no data satisfies the filter, there is no data in the index. When data is is added to the table, it's checked if it should be added to the index as well.
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 7:35 am
Thanks!
April 22, 2015 at 8:21 am
Hany Helmy (4/22/2015)
Realy don`t understand why can`t we use "Not In" for a filtered index while we can use "In" operator?! :w00t:
Only simple comparisons work.
NOT + <any operator> is not simple comparison.
I like how the author pointed out how useless the M$ Connect program has become.
They are worse than the BOL.
April 22, 2015 at 9:35 am
+1 to have found the correct post in the blog of Brent Ozar.
It is not the 1st time that the BOL documentation is "light" and incomplete.
I found the good answer because I am following the errors in Connect.
April 22, 2015 at 10:45 am
Hany Helmy (4/22/2015)
Realy don`t understand why can`t we use "Not In" for a filtered index while we can use "In" operator?! :w00t:
I'm actually more amused that 5 works and 6 doesn't, 8 works and 7 doesn't, 10 works and 9 doesn't since each pair is logically equivalent :hehe:
April 22, 2015 at 7:38 pm
Koen Verbeeck (4/22/2015)
Nice question, but a bit of a limited explanation.This article has more info:
I read that article after I got this question wrong. But in that article Jes says
You can have multiple items in the same where clause. I could have an index WHERE Status = ‘Open’ OR Status = ‘Shipping’.
Which lead me to think that #6 should have worked.
I can see why OR would be harder to implement than AND. Using IN as a workaround just feels quirky. I'll live with it.
ATBCharles Kincaid
April 22, 2015 at 11:35 pm
PHYData DBA (4/22/2015)
Hany Helmy (4/22/2015)
Realy don`t understand why can`t we use "Not In" for a filtered index while we can use "In" operator?! :w00t:Only simple comparisons work.
NOT + <any operator> is not simple comparison.
Thanx!
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
April 22, 2015 at 11:39 pm
Chris Harshman (4/22/2015)
Hany Helmy (4/22/2015)
Realy don`t understand why can`t we use "Not In" for a filtered index while we can use "In" operator?! :w00t:I'm actually more amused that 5 works and 6 doesn't, 8 works and 7 doesn't, 10 works and 9 doesn't since each pair is logically equivalent :hehe:
I guess it`s all about how MS defines simple & Non-simple comparison operators 😎
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
April 23, 2015 at 2:05 am
Carlo Romagnano (4/22/2015)
Good question!I do not like "Filtered indexes", they are too much limited. I prefer indexed view.
They are different tools for different purposes. You should use each where appropriate.
Koen Verbeeck (4/22/2015)
Nice question, but a bit of a limited explanation.This article has more info:
Good link. Additionally, the Books Online article on CREATE INDEX does describe exactly what can and cannot be done in a filtered index. The description is hard to read (as is often the case with syntax diagrams), but it is as far as I know 100% correct.
Hany Helmy (4/22/2015)
Realy don`t understand why can`t we use "Not In" for a filtered index while we can use "In" operator?! :w00t:
Simple answer: because the feature is built to work on a very limited subset of predicate logic.
If you think about the complexity for the optimizer to determine which indexes can be used for a query, you will soon realize that allowing "infinite" logic in the filtered index predicate would make the optimizer "infinite" complex. Restricting the filtered index simplifies the optimizer. So you have to draw a line somewhere, and that's what Microsoft did. And they chose to expose a very limited subset.
PHYData DBA (4/22/2015)
I like how the author pointed out how useless the M$ Connect program has become.They are worse than the BOL.
I agree in general on the Connect program being flawed, but not in this case. The Connect item is wrong. The limitations of filtered indexes are documented very precisely on the Books Online page on CREATE INDEX (https://msdn.microsoft.com/en-us/library/ms188783.aspx), and they show that the "bug" reported in the linked connect item is in fact not a bug.
Charles Kincaid (4/22/2015)
I read that article after I got this question wrong. But in that article Jes saysYou can have multiple items in the same where clause. I could have an index WHERE Status = ‘Open’ OR Status = ‘Shipping’.
Which lead me to think that #6 should have worked.
You are right, that is an error in her article. Aaron Bertrand already posted a comment to her article pointing out this mistake.
April 23, 2015 at 6:00 am
Hugo Kornelis (4/23/2015)
Additionally, the Books Online article on CREATE INDEX does describe exactly what can and cannot be done in a filtered index. The description is hard to read (as is often the case with syntax diagrams), but it is as far as I know 100% correct.
I think that's because there are lots of options and listing them all in one spot makes the page look cluttered and busy. In my opinion, this is still the best way to do it because you have it all laid out in front of you without having to flip between different pages and wonder what works with what. I think the business of the page is because of how much is supported by the statement.
BTW, nice question. Thinking about things and paying attention to the details is always important.
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply