When a query does not include scalar aggregates or require a final sorting step, a parallel query might return results in a different order from the same query run in serial, and subsequent executions of the same query in parallel might return results in different orders. The relative speed of the different worker processes leads to differences in result-set ordering. Each parallel scan behaves differently, due to pages already in cache, lock contention, and so forth. Parallel queries always return the same set of results, just not in the same order.
If you need a dependable ordering of results, use order
by, or run the query in serial mode.
In addition, due to the pacing effects of multiple worker processes reading data pages, two types of queries accessing the same data may return different results when an aggregate or a final sort is not done. They are:
Queries that use set rowcount
Queries that select a column into a local variable without sufficiently restrictive query clauses
