SQL SERVER – Group By Orders Data by Columns Ascending

SQL Server fundamentals can never get old. Just the other day, while working with a client on Comprehensive Database Performance Health Check, the DBA asked me an interesting question referring to my older blog post How to Know If Index is Forced on Query?. The question was about Group By Orders Data by Columns Ascending.

After reading the blog posts, DBA checked the Query Execution Plan after running a query. Incidentally, the query included the group by but it was not including any ORDER BY clause. However, when DBA checked the query, he was surprised to see that the column Ordered in the property said it is True.

He was surprised to see the results and asked me a question, why is he seeing the property Ordered as True when there is no ORDER BY clause is used. The answer is pretty simple.

Whenever we use the GROUP BY clause the data is automatically ordered by the columns used in the GROUP BY clause in the ascending direction, regardless of the original order of the table. This is how SQL Server and many other RDBMS are behaving for many years.

You can try it out by running the following script on the WideWorldImports sample database.

USE WideWorldImporters
GO
SELECT ContactPersonID, COUNT(OrderID) TotalCount
FROM [Sales].[Orders]
GROUP BY ContactPersonID
GO

Here is the output of the result:

You can clearly see that in the original table the column ContactPersonID is not ordered but in the query where we are using the GROUP BY clause the resultset is automatically ordered by the column. If you work with MySQL, MariaDB, PostgreSQL or SQL Server, you will see absolutely the same behavior of Orders Data.

It was surprising to me that many did not know about this feature when I discussed this the DBA. Let me ask you, did you know about this one? Please leave a comment with your answer. If you know any other such trick, I will be happy to publish on the blog with due credit to you.

Reference: Pinal Dave (https://blog.sqlauthority.com)