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.

SQL SERVER - Group By Orders Data by Columns Ascending ordered0-800x234

 

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.

SQL SERVER - Group By Orders Data by Columns Ascending ordered1

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:

SQL SERVER - Group By Orders Data by Columns Ascending ordered2

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)

SQL Group By, SQL Order By, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Upgrade Error: The Specified Service Does Not Exist as an Installed Service
Next Post
SQL SERVER – Getting Second Row from Table

Related Posts

3 Comments. Leave new

  • ERNESTO TORRES
    October 23, 2019 4:58 pm

    So, it is recomended to create an index on this column?

    Reply
  • Using the GroupBy does not inherently order the records returned, but forces the optimizer to use whatever index/ order the “grouped” by column has, in this case ASC

    Reply
  • This is good information about SQL Server group by and order by . thanks for sharing this information because this is very helpfull

    Reply

Leave a Reply