Filtered Index vs Indexed View
-
Comments posted to this topic are about the item Filtered Index vs Indexed View
-
Thanks
Hope this helps...
Ford Fairlane
Rock and Roll Detective
-
Nice questions...
-
Good one....
-
Could you please explain more details about the pass of query 4 and the fail of query 6?
-
I had the very same question, here's a post which discusses why local variables cause problems with matching to filtered indexes:
http://blogs.msdn.com/b/timchapman/archive/2012/08/27/the-drawback-of-using-filtered-indexes.aspx
-
jchu 39760 (1/15/2014)
Could you please explain more details about the pass of query 4 and the fail of query 6?The error 8622 is raised when you are searching a value in a table, BUT you instruct the optimizer to use a filtered index. Maybe, that the value exists in the table, but not in the filtered index. As you can see, this is a contradiction and an error is raised. The data in the table and in the filtered index may be different.
So the “SELECT * FROM table WITH(INDEX(idx_filtered)) WHERE id=99” asks for a row where id = 99 that exists in the table, but not in the filtered index: this is a contradiction.
At last, in the “SELECT * FROM table WITH(INDEX(idx_filtered)) WHERE id=@xxx”, the optimizer does not know the value of @xxx and if it is in the table, so it raises an error.
-
This was removed by the editor as SPAM
-
Excellent Carlo! Thanks for a great question.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn -
One more reason for me to avoid index query hints, unless it's completely necessary and throughly tested.
-
jchu 39760 (1/15/2014)
Could you please explain more details about the pass of query 4 and the fail of query 6?Query 2 (I know, you didn't ask) fails because you search for values < 5. Theoretically, there can be a value of -3 in the table. The filtered index would not include that value, so the optimizer is unable to produce a plan that is guaranteed to return correct results. For query 1, this objection is not relevant - you ask for values returned from the view, so not returning rows that are filtered out by the view condition is a given.
Query 4 asks for a specific value, and that value IS included in the filtered index. Based on the metadata, SQL Server can guarantee that all the rows you want returned will be in the filtered index.
Query 6 asks for the same value, but using a variable. The way the optimzer works means that when the query is parsed and a plan is compiled, the run-time value of the variable is unknown. There is no way to produce a plan that is guaranteed to return correct results for every value of the variable.
I was surprised to see that the question instructs people to run the query first. Should that not have been "without running the query, ..."? (That's how I did it - much more fun that way!)
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/ -
That was an excellent question; thank you for it. Query hints...you'd better know what you're doing. Most times it's better to let the optimizer do what it does best and optimize the query. Granted, it sometimes makes bad choices, but all in all I think it works pretty well.
-
Ed Wagner (1/15/2014)
That was an excellent question; thank you for it. Query hints...you'd better know what you're doing. Most times it's better to let the optimizer do what it does best and optimize the query. Granted, it sometimes makes bad choices, but all in all I think it works pretty well.+1. I try to let the optimizer do its work whenever possible. Great question Carlo.
-
Thanks for the excellent question.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply