Bert Wagner compares a couple methods for writing a query:
Suddenly those key-lookups become too expensive for SQL Server and the query optimizer thinks it’ll be faster to just scan the entire clustered index.
In general this makes sense; SQL Server tries to pick plans that are good enough in most scenarios, and in general I think it chooses wisely.
However, sometimes SQL Server doesn’t pick great plans. Sometimes the plans it picks are downright terrible.
If that particular topic is interesting, I’ve a blog post from a few years back on a similar vein.