Bhuvnesh (1/15/2013)
Grant Fritchey (1/15/2013)
as soon as the data changes or the inputs change, all bets are off.yes i have also realized that for smaller set , we can see the consistent data more often but what make it unexpected or randomly ordered for larger set (for the time being, we can keep out parallelism,out of site here ) ?
Random ordering will absolutely not occur unless you are not using an ORDER BY statement. Then, it's all over Microsoft documentation and the web, there's no guarantee for data order without an ORDER BY statement. It's that simple. Any number of things can affect the order of the data returned, page splits and rearrange, different execution plans based on changing statistics or changing parameters, more. If you don't use an ORDER BY, you can't be surprised when the order is different from one time to the next.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning