Can anyone come up with a plausible scenario where it would make sense to create a clustered index with, say, 10 or more columns?
No fair coming up with a poorly-normalized database or similar shenanigans.
I'm talking a real-world, well-designed table which actually needs 10+ columns in its clustered index.
Something to remember is that if there is an XML index on the table then the clustering key must be the primary key (see the BoL page CREATE XML INDEX, which says "A clustered index must exist on the primary key of the user table"). So if I have a long primary key I'm forced to have a long clustering key. I wouldn't generally choose a long clustering key, so if I had a long primary key I would not use it for clustering, but if I have an XML index on the table I don't have that getout - maybe have to use a surrogate for the natural primary key as the primary key, which could be a nuisance. But maybe even for a natural primary key, more than 10 columns seems a bit long, so perhaps it's not too much of a problem
The place where having an XML index will be a real pain (even if the primary key is short) is where it is desirable to have a natural primary key, and that will not be monotonic in time so using it as cluster key will result in a lot of page splits. Distinctly a pain, since having an XML index means the primary key has to be the cluster key. It pushes towards use of a surrogate as primary key even in cases where performace considerations make it sensible (in the absence of this consequence of having an XML index) to use the natural key. Fortunately my intense dislike of XML (caused by the outrageous hype and resultant overuse, not by anything in XML itself) means that I'm unlikely ever to be stuck with that sort of problem.