当订单列具有相同数据时，OrderBy 子句会产生不同的结果集 [英] OrderBy clause is resulting different result sets when order column having same data

问题描述

我们有一个存储过程来根据页码和页大小返回记录集.排序由CreateDateTime"列完成.如果所有记录的 CreatedDateTime 的值都相同，则以不同的顺序给出结果集.行为不一致.

We have a stored proc to return set of records based on Page Number and Page Size. Sorting is being done by a column "CreateDateTime". If value of CreatedDateTime is same for all the records, it is giving the results sets in different orders. The behavior is inconsistent.

部分代码:

SET @FirstRec  = ( @PageNo - 1 ) * @PageSize 
SET @LastRec   = ( @PageNo *@PageSize + 1 )   

SELECT * 
FROM 
( 
      select ROW_NUMBER() OVER (ORDER BY CreatedDateTime)     
       AS rowNumber,EMPID 
      From Employee
) as KeyList 
WHERE rowNumber > @FirstRec AND rowNumber <  @LastRec

请就此提供一些意见.

推荐答案

这是按设计"

如果未指定 ORDER BY 子句，则 SQL Server(或任何 RDBMS)不保证以特定顺序返回结果.有些人认为如果没有指定 order by 子句，行总是按聚集索引顺序或物理磁盘顺序返回.但是，这是不正确的，因为在查询处理期间有许多因素可以改变行顺序.并行 HASH 连接是改变行顺序的运算符的一个很好的例子.

SQL Server (or any RDBMS) does not guarantee results to be returned in a particular order if no ORDER BY clause was specified. Some people think that the rows are always returned in clustered index order or physical disk order if no order by clause is specified. However, that is incorrect as there are many factors that can change row order during query processing. A parallel HASH join is a good example for an operator that changes the row order.

如果指定ORDER BY 子句，SQL Server 将对行进行排序并按请求的顺序返回它们.但是，如果该顺序不是确定性的，因为您有重复的值，则在每个值组"中，顺序是随机的"，原因与上述相同.

If you specify an ORDER BY clause, SQL Server will sort the rows and return them in the requested order. However, if that order is not deterministic because you have duplicate values, within each "value group" the order is "random" for the same reasons mentioned above.

保证确定性顺序的唯一方法是在 ORDER BY 子句中包含有保证的唯一列或列组(例如主键).

The only way to guarantee a deterministic order is to include a guaranteed unique column or column group (for example the Primary Key) in the ORDER BY clause.

这篇关于当订单列具有相同数据时，OrderBy 子句会产生不同的结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！