Filtered Index vs Indexed View
-
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!)
Oh, I wanted people to get it right 100%.
😀
-
Luis Cazares (1/15/2014)
One more reason for me to avoid index query hints, unless it's completely necessary and throughly tested.The focus of the question is "How are filtered indexes usefull?".
My answer is "They are not so usefull as Indexed view".
If the filter is a little complex, the risk is that the optimizer does not use the index and starts a table scan (That's a problem on very populated table).
-
Very interesting one, Carlo. Thank you for the post. 🙂
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart. -
Carlo Romagnano (1/16/2014)
The focus of the question is "How are filtered indexes usefull?".My answer is "They are not so usefull as Indexed view".
Isn't this like saying that cars are more useful than scissors?
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/ -
Excellent question New thing learned today, the 6th option is a tricky one
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
Hugo Kornelis (1/16/2014)
Carlo Romagnano (1/16/2014)
The focus of the question is "How are filtered indexes usefull?".My answer is "They are not so usefull as Indexed view".
Isn't this like saying that cars are more useful than scissors?
I think more like comparing scissors to a guillotine. They both cut paper but one might chop your finger off if not used properly.
-
Sean Pearce (1/17/2014)
Hugo Kornelis (1/16/2014)
Carlo Romagnano (1/16/2014)
The focus of the question is "How are filtered indexes usefull?".My answer is "They are not so usefull as Indexed view".
Isn't this like saying that cars are more useful than scissors?
I think more like comparing scissors to a guillotine. They both cut paper but one might chop your finger off if not used properly.
+1
-
That was a nice one--though I have never encountered a case where I would intentionally use a filtered index on a table that also has an indexed view on it defined.
Generally I use filtered indexes as covering indexes--though I may be limiting optimization opportunities by doing so.
-
sneumersky (1/17/2014)
That was a nice one--though I have never encountered a case where I would intentionally use a filtered index on a table that also has an indexed view on it defined.Generally I use filtered indexes as covering indexes--though I may be limiting optimization opportunities by doing so.
I think that filtered index should be used simply with "flags/status".For example in a table with milions of records you can add a BIT column for identifying records to be processed.
create table t(id int, processed BIT NOT NULL DEFAULT 0)
create index idx_t on t(processed)
WHERE processed = 0
GO
--start processing
SELECT * FROM t
WHERE processed = 0
update t set processed = 1
where id = .......
-- end process
-
Sean Pearce (1/17/2014)
Hugo Kornelis (1/16/2014)
Carlo Romagnano (1/16/2014)
The focus of the question is "How are filtered indexes usefull?".My answer is "They are not so usefull as Indexed view".
Isn't this like saying that cars are more useful than scissors?
I think more like comparing scissors to a guillotine. They both cut paper but one might chop your finger off if not used properly.
I have to disagree with that.
A view (whether indexed or not) exposes only a part of the data in the base table (assuming it's a view on a single table and without aggregation). Selecting from the view or selecting from the tables are dramatically different, they can produce completely different results.
An index (whether filtered or not) is a performance instrument. It should never influence the results returned by the query. That is the whole reason why some of these queries return an error - you are telling SQL Server to perform an impossible task.
The comparison made earlier in this topic is a comparison between queries that potentially return different result sets. In that case, depending on the question that was asked, at least one of the queries is simply incorrect. That has to be fixed first, It's not until you have two queries that will always return the same result set that you can start makiing comparisons between the two - focusing on performance, maintainability, or portability, depending on your needs.
Cheers,
Hugo
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/ -
Good question.
Thanks
-
Absolutely stunning question on filtered indexes. Thanks Carlo. 🙂
-
Excellent question, learned something new today. It's absolutely logcial, but I've never thougth about it.
😀
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply