The paraphrasing you did on what I said makes it sound very dangerous. Here's what I actually said... (I've bolded the part you left out)...

I don't have a demonstrable example mostly because I never thought of building one but there are many examples that you could Google. For example, one example has to do with Table Scans (Clustered Index Scan in most cases) v.s. Index Seeks. I've seen many a query at work where someone added a new index to get rid of a CI Scan to get an Index Seek and it was absolutely the worst thing that could be done because the CI Scan was about 40 times more efficient than the 100,000 Index Seeks that occurred after the new index was added.

My point is that I don't want anyone to think that "INdexes is definitely a MUST thing here or everywhere" is the solution in all or even in most cases although I do agree that the presence of a proper Clustered Index is usually (but not always) a given. "It Depends" and someone must sit down and do some serious analysis and testing. Just adding a bunch of indexes to "solve" problems increases INSERT/UPDATE/DELETE latency (can actually produce timeouts for the front-end), can cause the optimizer to have to work harder to figure out which index is the most appropriate, can cause severe bloating of the database and the resulting backups (I have an audit table with 8GB of data and 14GB of indexes that I'm currently working on), nightly maintenance times can increase drastically (most NCI's fragment a lot because they are not in temporal order), and the unnecessary bloat also slows down restores in the even of a DR situation.