为什么SQL Server 2008在使用GROUP BY时订购并且没有指定订单？ [英] Why does SQL Server 2008 order when using a GROUP BY and no order has been specified?

问题描述

我遇到了一个很奇怪的问题，我还没有找到任何解释。使用SQL Server 2008并使用GROUP BY，它会在没有指定任何ORDER BY的情况下对我的列进行排序。

pre $ CREATE TABLE #Values（FieldValue varchar（50））

; WITH FieldValues AS
（
SELECT'4'FieldValue UNION ALL
SELECT'3'FieldValue UNION ALL
SELECT'2'FieldValue UNION ALL
SELECT' 1'FieldValue
）
INSERT INTO #Values（FieldValue）
SELECT
FieldValue
FROM FieldValues

- 第一个SELECT显示它们是已订购DESCENDING
SELECT
FieldValue
FROM #Values

- 第二个SELECT显示它们已订购ASCENDING
SELECT
FieldValue
FROM #Values
GROUP BY
FieldValue

DROP TABLE #Values


第一个SELECT将返回

  4 
 3 
 2 
 1 
  

第二个SELECT将返回

  1 
 2 
 3 
 4 
  

一个根据 MSDN文档，它指出： GROUP BY子句不排序结果集

解决方案

要回答这个问题，请查看两者生成的查询计划。

第一个SELECT是一个简单的表扫描，这意味着它会按分配顺序生成行。由于这是一张新表，它与您插入记录的顺序相匹配。

第二个SELECT添加了一个GROUP BY，SQL Server通过不同的排序实现，因为估计行数很低。如果您有更多的行或向您的SELECT中添加聚合，此操作符可能会更改。

例如，试试：

  CREATE TABLE #Values（FieldValue varchar（50））
 
; WITH FieldValues AS 
（
 SELECT'4'FieldValue UNION ALL 
 SELECT'3'FieldValue UNION ALL 
 SELECT'2'FieldValue UNION ALL 
 SELECT'1'FieldValue 
）
 INSERT INTO #Values（FieldValue）
 SELECT 
 A.FieldValue 
 FROM FieldValues A 
 CROSS JOIN FieldValues B 
 CROSS JOIN FieldValues C 
 CROSS JOIN FieldValues D 
 CROSS JOIN FieldValues E 
 CROSS JOIN FieldValues F 
 
 SELECT 
 FieldValue 
 FROM #Values 
 GROUP BY 
 FieldValue 
 
 DROP TABLE #Values 
  

由于行数，这会变成散列汇总，现在不存在排序在查询计划中。

如果没有ORDER BY，SQL Server可以返回结果y顺序，它返回的顺序是它认为它能够最快地返回数据的副作用。

I'm running into a very strange issue that I have found no explanation for yet. With SQL Server 2008 and using the GROUP BY it is ordering my columns without any ORDER BY specified. Here is a script that demonstrates the situation.

CREATE TABLE #Values ( FieldValue varchar(50) )

;WITH FieldValues AS
(
    SELECT '4' FieldValue UNION ALL
    SELECT '3' FieldValue UNION ALL
    SELECT '2' FieldValue UNION ALL
    SELECT '1' FieldValue
)
INSERT INTO #Values ( FieldValue )
SELECT
    FieldValue 
FROM FieldValues

-- First SELECT demonstrating they are ordered DESCENDING
SELECT
    FieldValue
FROM #Values

-- Second SELECT demonstrating they are ordered ASCENDING
SELECT
    FieldValue
FROM #Values
GROUP BY
    FieldValue

DROP TABLE #Values

The first SELECT will return

4
3
2
1

The second SELECT will return

1
2
3
4

According to the MSDN Documentation it states: "The GROUP BY clause does not order the result set"

解决方案

To answer this question, look at the query plans produced by both.

The first SELECT is a simple table scan, which means that it produces rows in allocation order. Since this is a new table, it matches the order you inserted the records.

The second SELECT adds a GROUP BY, which SQL Server implements via a distinct sort since the estimated row count is so low. Were you to have more rows or add an aggregate to your SELECT, this operator may change.

For example, try:

CREATE TABLE #Values ( FieldValue varchar(50) )

;WITH FieldValues AS
(
    SELECT '4' FieldValue UNION ALL
    SELECT '3' FieldValue UNION ALL
    SELECT '2' FieldValue UNION ALL
    SELECT '1' FieldValue
)
INSERT INTO #Values ( FieldValue )
SELECT
    A.FieldValue
FROM FieldValues A
CROSS JOIN FieldValues B
CROSS JOIN FieldValues C
CROSS JOIN FieldValues D
CROSS JOIN FieldValues E
CROSS JOIN FieldValues F

SELECT
    FieldValue
FROM #Values
GROUP BY
    FieldValue

DROP TABLE #Values

Due to the number of rows, this changes into a hash aggregate, and now there is no sort in the query plan.

With no ORDER BY, SQL Server can return the results in any order, and the order it comes back in is a side-effect of how it thinks it can most quickly return the data.

这篇关于为什么SQL Server 2008在使用GROUP BY时订购并且没有指定订单？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！