Jeff and Gila,
Thanks for the clarifications. I take it you're saying that even with a MAXDOP 1 on the simple SELECT it doesn't guarantee row ordering.
My question was mostly speculative because I would always write it with an ORDER BY to be certain. I probably should have added a "usually" to my comment.
However I would be interested to see a case where a simple SELECT doesn't return rows ordered by the CI. I am not doubting either of you when I say this, just trying to learn more about it.
I also realize that doing other things in the SELECT (e.g., JOIN to another table) would certainly lower the likelihood of rows returned in the CI order.
Jeff - I am unclear on where to find the article you referenced from Paul White.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St