Select WITHOUT WHERE 或 ORDER BY 子句的结果排序顺序 [英] Sort Order of results for Select WITHOUT WHERE or ORDER BY Clause

问题描述

我有一个带有 PK 聚集索引以及其他索引的表，包括唯一的和非唯一的.如果我发出(完全):

I have a table with a PK clustered index as well as other indexes on it, both unique and non-unique. If I issue (exactly):

SELECT * FROM table_name

或

SELECT col1, col2 FROM table_name

以什么顺序返回行?

这是客户转发给我们的面试问卷中的第一个问题.以下是说明:

This is the first question in an interview questionnaire a customer has forwarded us. Here are the instructions:

如果此问题的答案有误，请立即终止面试！个人，无论他们声明的能力如何，都不了解基于 SQL 的关系数据库管理系统.这是过去 25 多年的 SQL-101 逻辑.正确答案是:未知/随机/不确定，因为没有指定 ORDER BY 子句作为查询的一部分".

If the answer to this question is incorrect, terminate the interview immediately! The individual, regardless of their stated ability does not understand SQL-Based relational database management systems. This is SQL-101 logic for the past 25+ years. The correct answer is: "unknown/random/undetermined because no ORDER BY clause was specified as part of the query".

我不相信这实际上是正确的.欢迎所有评论.

I am somehow not convinced that this is actually correct. All comments welcome.

谢谢，

拉杰

推荐答案

即使表有主键/聚集索引，您也无法确定行的顺序.尽管在执行计划中最后会有索引/堆扫描，但如果查询在多个核上并行执行，由于并行流合并计划步骤，结果数据集将不会被排序.

Even if a table has a primary key/clustered index, you can't be sure about the order of rows. Although in the execution plan there will be an index/heap scan at the end, if query is performed in parallel on many cores, the resulting dataset won't be sorted due to parallel streams merge plan step.

您可能不会在小型数据库上看到它，但请尝试在不同的硬盘驱动器上创建一个包含多个文件的数据库，并在多核机器上运行一个简单的查询.您很可能会得到按 ID部分排序"的结果 - 即会有块对行进行排序，但块将以半随机顺序检索.

You probably won't see it on small databases, but try creating one with many files on separate harddrives and run a simple query on a multicore machine. Most likely you'll get results "partialy sorted" by ID - i.e. there will be blocks where rows are sorted, but blocks will be retrieved in semi-random order.

这篇关于Select WITHOUT WHERE 或 ORDER BY 子句的结果排序顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！