Here's a weird situation where having a clustered indexed view was a life saver --

At my last job, we had a table that stored notes for claims. This table was poorly designed, in that diary activities (i.e. things needing to be done on the claim) were mixed in with the notes. There were 40 million rows, of which only about 100,000 at any time were diary activities. I had no ability to change this as virtually everything the company did used that table and would have to be changed.

That table was clustered on claimid, which made sense for core application functionality, since the app brought up a claim and always looks at notes as an attribute of claims. No performance problems. However, over time, the business wanted a dashboard for adjusters that would show all of their diary actions in one view. This view would require looking at between several dozen to several hundred (maybe even a few thousand) rows in the notes table. However, those rows would be scattered throughout the table, and even though the index would tell you what rows you needed, the dashboard then required a crippling bookmark lookup to get the notes data needed. A covering index would in essence be a second copy of the whole table.

Solution: a clustered indexed view, clustered on adjusterid, where the query ONLY returned the diary activities. Yes, it stored the rows in full a second time on disk, but it was only 100,000 as opposed to 40 million rows. Performance wasn't ever a real problem because of how the business used them (typically only one user at a time in their notes) so no blocks or deadlocks, and update and delete time was acceptable. It was the only solution we could find to handle a situation where basically two clustered indexes were needed.

And no, starting from scratch, we'd have NEVER done that, at least not on my watch.