THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Rob Farley

- Owner/Principal with LobsterPot Solutions (a MS Gold Partner consulting firm), Microsoft Certified Master, Microsoft MVP (SQL Server), and leader of the SQL User Group in Adelaide, Australia. Rob is a former director of PASS, and provides consulting and training courses around the world in SQL Server and BI topics.

Why isn’t my filtered index being used?

Hi! - Great that you've found this page, but it's no longer here! You can find the content over at:

Published Sunday, February 22, 2015 10:42 PM by Rob Farley



Alvin Park said:

Well how can I register myselg on this site??

February 23, 2015 4:53 AM

Marcel said:

You can use parametrized dynamic SQL alternatively which is immune to injection.

February 24, 2015 5:47 PM

Rob Farley said:

Hi Marcel,

Yes (as per my post that I linked to above), but if you use parameters (with OPTION (RECOMPILE)) then the QO needs to come up with a plan that is general and is unlikely to use your filtered index.

February 24, 2015 6:03 PM

Matt said:

Nice article, this is quite subtle but very useful knowledge that needs to be thought about.

If filtered indexes are susceptible to this problem then is it a good alternative idea to embed the filter clause in a view, index the view and then query on the view? Clearly the developer has to know how to make this work safely but this may well satisfy a lot of situations where the filtered index might be applied.

June 21, 2016 6:24 AM

Rob Farley said:

Oh Matt - I'm totally in favour of using views to help developers make good decisions about predicates they should use. :)

June 21, 2016 6:29 AM
Anonymous comments are disabled

This Blog



No tags have been created or used yet.


News? Haven't you read my blog?

My Company

Can't find something?

Contact Me

Twitter: @rob_farley
Skype: rob_farley

MVP (SQL Server)


Adelaide SQL UG


Privacy Statement