I've recently had a discussion with a colleague who was pushing to remove order by clauses from a production query because the order by column was the same as the primary key.

After a lengthy discussion in which I tried to explain that he can't guarantee ordering based on the primary key the final conclusion was that he wasn't going to push for the MSSQL queries to be changed.
But he was still going to change the DB2 queries.

I couldn't immediately find an article disproving that DB2 orders queries by the primary key, and am currently wondering whether or not it does.

So my question is, how does DB2 order a query if there is no order by clause? Does it use the primary key?
How can you guarantee data is coming out ordered correctly, without an order by clause, in a parallel system?

migrated from serverfault.com Dec 23 '14 at 10:26

This question came from our site for system and network administrators.

up vote 18 down vote accepted

No, your colleague is wrong.

All SQL proroducts - DBMS that behave according to the SQL standards - provide no guarantee that the result of a query output will be ordered in any way, unless there is an ORDER BY clause in the query.

As the IBM DB2 docs mention:

Ordering is performed in accordance with the comparison rules described in Language elements. The null value is higher than all other values. If your ordering specification does not determine a complete ordering, rows with duplicate values of the last identified sort-key have an arbitrary order. If you do not specify ORDER BY, the rows of the result table have an arbitrary order.

  • "If they behave by SQL standards" is a big if... IBM docs FTW. – WernerCD Dec 23 '14 at 16:29
  • @WernerCD No DBMS complies 100% with the standard (and there are many versions of it.) But they claim to comply with many parts. I expect to at least have where they differ, documented. – ypercubeᵀᴹ Dec 23 '14 at 16:31
  • Yeah, that's why I was +1 for IBM docs over "Standards". "Expected" vs "Actual" can, and most definitely will, bite you in the keister. – WernerCD Dec 23 '14 at 16:53

As was pointed out in ypercube's answer, when there is no ORDER BY clause there is no defined order.

What I would like to add is that it's important to realise that SQL is very much an abstraction, it does not specify step by step what the DBMS is to do but rather specifies your requirements of the end result.

This implies that if the data is already looked up in a way that ensures the correct order, this is so because the DBMS decided on such a strategy and it will then already know that no additional sorting work is necessary to meet the requirements of the ORDER BY clause.

On the other hand, if you do not specify that the order is significant to you (even though it actually is?), the DBMS will take this into account and if it finds a different strategy that is more efficient to find the right data in some other order it will take advantage of the fact that you apparently did not care about the order. (The chosen strategy could very well change over time as well, based on increasing amount of data in the relevant tables, software patches, etc.)

Ie, removing the ORDER BY clause has very little upside (slightly smaller query size) but potentially huge downside if the order is actually important. Especially so, as this could very well work with the test dataset and get through acceptance testing only to cause serious problems down the line.

  • 1
    The reason he gave on his deployment sheet is that "Ordering by is taking up CPU resources, while the order is already implied by the primary key". Which sounded very wrong to me (as proven by the answers so far) – Reaces Dec 23 '14 at 11:45
  • @Reaces does DB2 use 'heap' tables by default or is it more like SQL Server with clustered indexes? It doesn't change the answer, but it may make it easier to prove :) – Jack Douglas Dec 23 '14 at 13:05
  • @Jack Douglas, clustering in SQL Server and DB2 are different concepts. I learned a lot regarding clustering in SQL server in this thread: groups.google.com/d/msg/comp.databases.ms-sqlserver/P7Wcs4NcF4s/…. I you are familiar with SQL server but not with DB2 you may want to have a look. – Lennart Dec 23 '14 at 13:38
  • @Reaces tables in DB2 are usually "clustered" on the primary key by default, unless a clustering index is specified. – Chris Aldrich Dec 23 '14 at 13:44
  • 1
    @ChrisAldrich -- this statement is only true for DB2 on z/OS. – mustaccio Dec 23 '14 at 14:58

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.