SQL Server Trick: Circumvent Missing ORDER BY Clause

Posted on by lukaseder
SQL Server is known to have a very strict interpretation of the SQL standard. For instance, the following expressions or statements are not possible in SQL Server: 

-- Get arbitrarily numbered row_numbers
SELECT ROW_NUMBER() OVER ()

-- Skip arbitrary rows
SELECT a
FROM (VALUES (1), (2), (3), (4)) t(a)
OFFSET 3 ROWS
Strictly speaking, that limitation makes sense because the above ROW_NUMBER() or OFFSET expressions are non-deterministic. Two subsequent executions of the same query might produce different results. But then again, any ORDER BY clause is non-deterministic, if you do not order by a strictly UNIQUE expression, such as a primary key. So, that’s a bit of a pain, because other databases aren’t that strict and after all, you might just not care about explicit ordering for a quick, ad-hoc query, so a “reasonable”, lenient default would be useful.

Constant ORDER BY clauses don’t work

You cannot add a constant ORDER BY clause to window functions either. I.e.: 

-- This doesn't work:
SELECT ROW_NUMBER() OVER (ORDER BY 'a')

-- But this does!
SELECT a
FROM (VALUES (1), (2), (3), (4)) t(a)
ORDER BY 'a'
OFFSET 3 ROWS
Note that ORDER BY 'a' uses a constant VARCHAR expression, not a numeric one, as that would be generating column-reference-by-index expressions, which would be non-constant in the second example.

Random column references don’t work

So you’re thinking that you can just add a random column reference? Sometimes you can, but often you cannot: 

-- This doesn't work:
SELECT ROW_NUMBER() OVER (
  ORDER BY [no-column-available-here]
)

-- But this does!
SELECT a
FROM (VALUES (1), (2), (3), (4)) t(a)
ORDER BY a
OFFSET 3 ROWS
The above examples show that you do not always have a column reference available in any given SQL expression. There is no useful column that you could refer to from the ROW_NUMBER() function. At the same time, you can write ORDER BY a in the second example, but only if a is a “comparable” value, i.e. not a LOB, such as text or image. Besides, as we don’t really care about the actual ordering, is it worth ordering the result set by anything at all? Do you happen to have an index on a?

Quasi-constant ORDER BY expressions do work

So, to stay on the safe side, if ever you need a dummy ORDER BY expression in SQL Server, use a quasi-constant expression, like @@version (or @@language, or any of these). The following will always work: 

-- This always works:
SELECT ROW_NUMBER() OVER (ORDER BY @@version)

-- So does this:
SELECT a
FROM (VALUES (1), (2), (3), (4)) t(a)
ORDER BY @@version
OFFSET 3 ROWS
From the upcoming jOOQ 3.4, we’ll also generate such synthetic ORDER BY clauses that will help you simplify writing vendor-agnostic SQL in these edge-cases, as we believe that you simply shouldn’t think of these things all the time.

Published by lukaseder

I made jOOQ

11 thoughts on “SQL Server Trick: Circumvent Missing ORDER BY Clause”

  1. I only use a couple of database engines and it’s tricky already…It’s so hard to know all that stuff for all providers, so posting all these tips and tricks will make our lives so much easier, the next time we bump into any of these issues.

    Reply

    1. so posting all these tips and tricks will make our lives so much easier, the next time we bump into any of these issues.

      Nah, just use jOOQ and forget about these tips again ;-)

      Reply

      1. That’s a good point. My JPA to jOOQ mapping article is the most constantly daily accessed post. SQL has much to offer and jOOQ feels natural, especially after being so much habituated to JPA. I think the Hibernate “masters” will easily see all the advantages of jOOQ, and the rest will simply follow. What do you think, am I right on this one?

        Reply

        1. My JPA to jOOQ mapping article is the most constantly daily accessed post.

          You could become an expert in that area! :-) But I do believe that you get some traction on that. We constantly promote it on social media, and we haven’t covered the subject much yet, ourselves.

          If I had a little more time, I would delve into a CQRS architecture that combines JPA with jOOQ in a single application, where JPA does the Command (C) part and jOOQ does the Query (Q) part. Your post dealing with combined code generation is an awesome headstart.

          An awesome integration would be to find a way how jOOQ’s ConnectionProvider (or some other SPI) could be leveraged to redirect all jOOQ statements to an EntityManager‘s createNativeQuery() API. Possibly, jOOQ-generated queries could directly bind variables on such a javax.persistence.Query instead of a JDBC PreparedStatement, sharing transaction managers and entity mapping algorithms, etc. What do you think?

          SQL has much to offer and jOOQ feels natural, especially after being so much habituated to JPA. I think the Hibernate “masters” will easily see all the advantages of jOOQ, and the rest will simply follow. What do you think, am I right on this one?

          From my experience, Java / Hibernate “masters” have yet to understand the power of SQL, which they have been trying to hide all along. This will all sink in slowly, but steadily. But even if SQL is great for some use-cases, it’s at best mediocre for others. So the ultimate combo (of my dreams, at least) would be a joint library: Hibernate + jOOQ. We’ll certainly be working towards such a goal…

          Reply

          1. You are right, JPA and jOOQ should have a binding mechanism and integrating it into EntityManager would simplify its usage, considering how many JPA applications are already running in production systems.

            As for the JPA “masters”, if someone has taken the time and effort to understand Hibernate and its inner workings, he/she would realize its limitations and appreciate the value of jOOQ advanced querying mechanism.

            I don’t think I ever met a JPA domain expert that wasn’t also an SQL aficionado, those things are inseparable anyway. Understanding means discovering strengths and weaknesses and JPA/ORM is no different.

            The first time I browsed Martin Fowler’s EAA book, I was amazed how many of those patterns we now get for free by just using Hibernate: “Unit of Work”, “Optimistic locking”, “Dirty checking”, “Work flows”, “Long conversations”.

            That’s the reason I think Hibernate is not an issue “per se”. The biggest problem is the lack of understanding combined with massive adoption, a recipe for failure. But this happened/happens with any other technologies as well: J2EE, NoSQL.

            “Ditching JPA to start from scratch” reminds me of Joel Spolsky article about software rewrite (http://www.joelonsoftware.com/articles/fog0000000069.html):

            “The idea that new code is better than old is patently absurd. Old code has been used. It has been tested. Lots of bugs have been found, and they’ve been fixed. There’s nothing wrong with it. It doesn’t acquire bugs just by sitting around on your hard drive. Au contraire, baby! Is software supposed to be like an old Dodge Dart, that rusts just sitting in the garage? Is software like a teddy bear that’s kind of gross if it’s not made out of all new material?”

            Or Uncle Bob on Framework Bound (http://blog.8thlight.com/uncle-bob/2014/05/11/FrameworkBound.html):

            “This shouldn’t be surprising. Frameworks are written by people to solve certain problems that they have. Those problems may be similar to yours, but they are not yours. You have different problems. To the extent that your problems overlap, the framework can be enormously helpful. To the extent that your problems conflict, the framework can be a huge impediment.”

            Hibernate has been in use for over a decade, and from one project to the other it came to address many common problems, the ones we all face in many projects.

            Getting to know JPA is not an easy ride, but is SQL any different? Or proper transaction management? All those take time and effort to properly master, and if one is not willing to invest, he won’t get any profit out of using them.

            Reply

            1. Hehe. Don’t get me wrong. I’m absolutely along the lines of your statement here:

              That’s the reason I think Hibernate is not an issue “per se”.

              No one said that JPA should be abandoned or re-written.

              But I’m also pretty sure that “NamedEntityGraph” solves problems that only very few people have and than even less people should have. I’m very surprised, also, about the verbose implementation of stored procedure support in JPA 2.1 that provides only little improvement over stored procedure support in JDBC. About as much as I am still surprised about the Criteria API. If you have JPQL, why would you build a Criteria API that doesn’t really model JPQL?

              So, I’m quite sure that JPA did take a couple of funny turns. You’ve listed that here:

              To the extent that your problems conflict, the framework can be a huge impediment.

              And then:

              Getting to know JPA is not an easy ride, but is SQL any different?

              Yes, of course it is different :-) You said it yourself. If you want to master JPA, you better master SQL as well. If you want to master SQL, you only need to master … SQL. Nevertheless, as I said, I agree with you that JPA solves many problems very well, and I most certainly agree with you that massive adoption has lead to many problems, as people took Hibernate / JPA for something that it isn’t. And that’s why we’re here. We provide that other feature set. The “R” in CQRS.

              Reply

              1. A CQRS example would be great blog post, the major challenge is to maintain the ACID properties between command and query. The pattern doesn’t imply an asynchronous event propagation and “eventual consistency” is a trade-off most application don’t really mandate.

                I will have to evaluate the new JPA 2.1 changes myself. After “Criteria API”, I am sceptical about JPA new features.

                Reply

                1. maintain the ACID properties between command and query

                  True. I guess if you only use RDBMS-centric transactions (and no cache), you’ll be safe. Or so…

                  I will have to evaluate the new JPA 2.1 changes myself. After “Criteria API”, I am sceptical about JPA new features.

                  You are warned! :-) All I’m saying is: Annotatiomania™. And stored procedures.

                  Reply

Leave a Reply to lukaseder Cancel reply