SQL Server 2008 - Filtered Indexes

Say I have an index defined as follows:

CREATE NONCLUSTERED INDEX [IX_Marker] ON [dbo].[Marker] 
(
    [Run] ASC,
    [EquipmentID] ASC,
    [ReadTime] DESC
)
INCLUDE ( [Sequence]) 
WHERE ([ReadTime]>'07/01/2011')

Under what circumstances will the SQL Server plan generator select this ind开发者_StackOverflow社区ex? For example, say I have the following query:

Select * From Marker Where ReadTime > '3/1/2011'

I assume the index wouldn't be used in this case? But if I changed the Where clause to '8/1/2011', it would get used?

The index will be used when it includes a superset of the records needed in the query, not a subset.

Basically if the engine KNOWS or SUSPECTS that the index is excluding records that will potentially be needed in the result set, it won't use that index.

also, just a word of caution - your filtered index will be used only if the where clause has the date value hard-coded. If you use a parameter (either thro a parametrized query or a SP) in your where clause the filtered index will not be used.

So if you have:

declare @d date = '8/1/2011'
Select * From Marker Where ReadTime > @d

In above case the filtered index will not be used.