Hugo Kornelis (7/19/2010)

---

Jeff Moden (7/19/2010)

---

Hugo,

The gain is that there is nothing you can naturally order by that will give you the correct original order. 😉

Huh?

USE AdventureWorks2008;

SELECT TerritoryID, GROUPING(TerritoryID),

SalesPersonID, GROUPING(SalesPersonID),

SUM(TotalDue) AS SumOfTotalDue

FROM Sales.SalesOrderHeader

GROUP BY ROLLUP(TerritoryID, SalesPersonID)

ORDER BY GROUPING(TerritoryID), TerritoryID,

GROUPING(SalesPersonID), SalesPersonID;

And stop playing the "gotcha back" tricks... can you make the natural sort fail or not?

Whether or not I can is completely irrelevant. I advise people to include the ORDER BY, because it guarantees correct results. You advise people to omit the ORDER BY clause; in my opinion that places the burden on you to prove that you will never need it. Not even after installing a service pack or upgrading to the next version of SQL Server. Can you guarantee that?

Heck, can you even guarantee that this will always work on the current version of SQL Server? I'm on holiday, with only a simple laptop computer, so my testing capacity is limited. But imagine a SQL Server instance on a computer with 16 cores, with Sales.SalesOrderHeader partitioned by TerritoryID and spread over 16 seperate spindles, and with the amount of data in that table bumped to several billion rows. I would expect (and, in fact, even HOPE) that each core gets to process the data for a single partition. Without the ORDER BY, the data will be returned as soon as it's ready. Why would SQL Server wait for core #1 to finish if core #5 already has some data ready to be returned?

Again - if you advise people to rely on undocumented behaviour, the burden is on you to prove that it will work in all cases. And that it will continue to work in the future. If you can't, and you still keep giving the same advise, you are repeating the mistake many people made in the days of SQL Server 6.5 when they told people to omit the ORDER BY after a GROUP BY.