Coming late to the party, but...

The charindex expression is bound to scane the entire table.

LIKE '9000413237%' will seek the index in a normal way.

LIKE '%9000413237%' will also result in an Index Seek operator, but if you look closer there is a RangeScan function in the search predicate. And of course, while a Seek in the name, the only way to find the arbitrary string is to scan the entire index. But assuming that this only hits a few rows, this is more efficient than scanning the clustered index, since there are fewer pages to scan. SQL Server maintains string statistics, so it has a hinch about the hit rate you will get in the index.