In your article you write:

"You might be surprised to learn that changing the WHERE clause in the previous statement from "SpecialOfferID <> 1" to "SpecialOfferID = 2" will prevent SQL Server from using the filtered index. This is because SQL Server compares the WHERE clause of the SELECT statement against the WHERE clause of the CREATE INDEX statement for lexical equivalence, not for logical equivalence. Therefore, SQL Server does not realize that the filtered index covers the query."

While this is true, it only affects queries with predicates containing the same column(s) used in the filtered index predicate. Moreover, it should be pointed out that including the columns used in the filtered index predicate (SpecialOfferID in this case) either in the index key or included columns, will allow the optimizer to consider the filtered index (provided it is valid for the query predicate of course).

ie:

CREATE NONCLUSTERED INDEX FK_ProductID_ModifiedDate

ON Sales.SalesOrderDetail (ProductID,ModifiedDate)

INCLUDE (OrderQty,UnitPrice,LineTotal,SpecialOfferID) -- NOTE the addition of the predicate-column)

WHERE SpecialOfferID <>1

Is valid and will be used for the following query:

SELECT ProductID ,

ModifiedDate ,

SUM(OrderQty) 'No of Items' ,

AVG(UnitPrice) 'Avg Price' ,

SUM(LineTotal) 'Total Value'

FROM Sales.SalesOrderDetail

WHERE SpecialOfferID = 2

GROUP BY ProductID ,

ModifiedDate

Of course the optimizer may choose not to use the filtered index if the query predicate is not selective enough in which case you may decide to force the optimizer's hand with an index hint:

...WITH (INDEX(FK_ProductID_ModifiedDate)) WHERE SpecialOfferID = 2 ...

While index hints can force a plan to utilize a filtered index (forcing even our filtered index without an 'INCLUDED SpecialOrderID' to be used) they should not substitute well-designed indexes. If the optimizer isn't selecting your filtered index, check if it's covering the query. Would a non-filtered index be valid and cover the same query? If not, try adding the column(s) used in the query predicate to the filtered index's included columns (or key if appropriate). If this doesn't help, check your cardinality estimates. Are your stats up to date? etc...

David's done a stirling job on this Stairway so it's disappointing that he's not kept to the same high standard on this level. Many more explanations and considerations on filtered indexes can be found at: http://msdn.microsoft.com/en-us/library/cc280372(v=sql.105).aspx