SQL中的无序结果 [英] Unordered results in SQL

问题描述

我已经一遍又一遍地了解到，SQL本质上是无序的模型.这意味着多次执行相同的SQL查询可以按不同的顺序返回结果集，除非其中包含"order by"子句.有人可以解释为什么SQL查询在运行查询的不同实例中以不同的顺序返回结果集吗?并非总是如此，但肯定有可能.

I have read it over and over again that SQL, at its heart, is an unordered model. That means executing the same SQL query multiple times can return result-set in different order, unless there's an "order by" clause included. Can someone explain why can a SQL query return result-set in different order in different instances of running the query? It may not be the case always, but its certainly possible.

从算法上讲，当没有"order by"子句时，查询计划在确定结果集的顺序中不起作用吗?我的意思是，当有针对某个查询的查询计划时，该算法如何不总是以相同的顺序返回数据?

Algorithmically speaking, does query plan not play any role in determining the order of result-set when there is no "order by" clause? I mean when there is query plan for some query, how does the algorithm not always return data in the same order?

注意:我不是在质疑订单的使用，而是在问为什么没有保证，例如，我试图理解由于没有任何保证而引起的挑战.

Note: Am not questioning the use of order by, am asking why there is no-guarantee, as in, am trying to understand the challenges due to which there cannot be any guarantee.

推荐答案

一些SQL Server示例，其中完全相同的执行计划可以返回不同顺序的结果.

Some SQL Server examples where the exact same execution plan can return differently ordered results are

1. 可以在旋转扫描功能允许扫描以在并发查询之间共享.
2. 并行计划通常是不确定的，结果的顺序可能取决于运行时选择的并行度和服务器上的并行工作量.
3. 如果计划具有无序预取的嵌套循环，则此

1. An unordered index scan might be carried out in either allocation order or key order dependant on the isolation level in effect.
2. The merry go round scanning feature allows scans to be shared between concurrent queries.
3. Parallel plans are often non deterministic and order of results might depend on the degree of parallelism selected at runtime and concurrent workload on the server.
4. If the plan has nested loops with unordered prefetch this allows the inner side of the join to proceed using data from whichever I/Os happened to complete first

这篇关于SQL中的无序结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！