This is undocumented feature and should not be used. First of all, it doesn't respect the ORDER BY, secondly it has problems with NULL values. So, I would not recommend to do this in a production code.

You may check MSDN T-SQL forum and posts by Erland Sommarskog if you don't believe me.

Do you have an actual link?

It doesn't seem to respect a clustered index but I'm pretty sure it respects an ORDER BY. (At least it does in the examples I've seen/used)

As for NULL values - while I won't say "never", the context in which this often is used is for business keys, surrogate keys, or primary descriptors. Most likely they'd be constrained to not permit null values. Again, not an absolute.

So I'll say it this way, I've never seen it "not work" (and I've seen plenty of people doing it in production) on non-null columns with an ORDER BY. If there's an example otherwise, I'd definitely be interested in seeing it/knowing about it.

Thanks...