This is a contrived example, but it is a common one that I have seen.

What happen in the query is that the ordering of the index initially was ModifiedDate, CarrierTrackingNumber, ProductID. But the query was only able to use the first column ModifiedDate in the index to perform a seek due to the ISNULL function not allowing SQL to properly get the value in the second column CarrierTrackingNumber. If the query is unable to get to the second column in index, basically it can't get to the third column as well. However in the query plan, you'll still observe an Index Seek operator. Its only when you scroll your mouse over the Index Seek operator that you'll find the index is not as effective as it should.

It is not as effective because its only using the first column in the index seek, then underneath performs a filter which has to scan all rows where ModifiedDate = '1 Aug 2007' to just return the one record which you get in your output. This is kind of quite similar to having ModifiedDate as an index and both CarrierTrackingNumber and ProductID as included columns.

There are numerous ways to tune the query and it is very difficult to describe each and every solution. By understanding what the example has provided, ModifiedDate and ProductId is still SARGable. So, if you create an index in the ordering ModifiedDate, ProductId, CarrierTrackingNumber (or ProductId, ModifiedDate, CarrierTrackingNumber ) then you would have reduced the number of residual rows that needs to be filtered from the Index Seek.

In this article, I just wanted to point out that an Index Seek operator doesn't mean all columns in the index definition are used to "seek" and return the result. The predicate in an Index Seek operator indicate there are residual rows that needs to be filtered after a "seek" on an index.

Hope this clarifies and doesn't confuse further.