Quassnoi,

I don't have time this morning for a full reply, and I'm off around the island for the next few days, so this will be just a quick one. I promise a fuller reply when I get back.

The reference you need is http://sqlblog.com/blogs/kalen_delaney/archive/2008/03/16/nonclustered-index-keys.aspx - a blog entry by Kalen Delaney.

The point about the Segement Top is that it is very fast on all scenarios, and doesn't require any special tricks or coding to just work well. It also works for the other cases in your blog entry too (without ties etc).

The problem with the other methods in your blog entry is that they depend on data distribution, and perform poorly if the distribution is 'wrong'.

Naturally, if one knows something about the data distribution, and can guarantee that it will never change, it might be worthwhile considering a hand-coded 'trick' like the recursive CTE for extremely large data sets, though I think this is very much an edge case to be honest, and might be too fragile for production use.

I think a general method with very good and predictable performance that uses the statistics available to the optimizer to produce an appropriate plan over a wide set of circumstances should be preferred in the general case. Segment Top provides such a method for this class of query, and you really should include it in your blog and tests.

I have many other things to say on the specifics of your last reply, but they will have to wait.

Cheers

Paul