Why Order Isn’t Guaranteed Without an ORDER BY

Start with the Stack Overflow database, with no nonclustered indexes. If you’ve been playing around with demos, run my DropIndexes proc just to clear things out, which leaves the clustered indexes in place.

Say you run this query:

By default, with a clustered index on the Id field (identity, starts at 1 and goes up to a bajillion), your results will probably look like this, ordered by Id:

Because if you look at the execution plan:

SQL Server is doing a clustered index scan. Since we’re clustered on Id, the results happen to come back in Id order.

But watch what happens when I create an index on DisplayName and Id, and then run the exact same query again:

Now the data comes back ordered by DisplayName due to the way SQL Server ran the query, as shown in the execution plan:

As I talk about in How to Think Like the Engine, SQL Server doesn’t have to do a clustered index scan for queries like these. It can use the smallest copy of the table that achieves its objectives, and in this case, it’ll use the nonclustered index because it takes less time to scan.

If you have to scan the Users table to get DisplayName and Id, which of these two objects would you rather scan:

The clustered index is 58MB, but our index on DisplayName and Id is only 9MB. It’s faster to scan that nonclustered index.

SQL Server may use indexes you didn’t expect.

Say we’ve got an index on DisplayName that happens to include Reputation, and we frequently run this query:

Whenever we run the query, we always get the data back sorted by DisplayName, so we assume that it’s always going to be that way:

But that’s just because SQL Server happened to use the index on DisplayName that includes Reputation:

Because SQL Server knows a whole lotta users have Reputation = 1, so it can scan that index and quickly find the rows it needs. It doesn’t need to scan the ENTIRE index, just enough of it to find 100 users with Reputation = 1.

But the instant we create a “better” index – one that allows a seek, for example – and say that we only include DisplayName (not sort it):

The data doesn’t come back ordered. SQL Server did an index seek on our new index:

But since DisplayName was only included, not sorted, even just a 100-row result set doesn’t come back in the order you might expect.

So in summary: if you need stuff ordered, add an ORDER BY, or else your order can get changed based on the execution plan SQL Server chooses.

Previous Post
Which Queries Should You Cache in the Application?
Next Post
Building SQL ConstantCare®: Letting You Mute Recommendations

5 Comments. Leave new

  • Can parallelism impact ordering too?

    Reply
  • ha remember this pre SQL Server 2005 nonsense

    create view select top 100 percent order by..

    Glad that went away too but I believe there is a trace flag that will enable that behavior (some shop must have paid some good money for that one)

    Reply
    • Brian Boodman
      May 15, 2019 1:05 pm

      The trick is to use SELECT TOP 999999999 instead (add more 9s if necessary).
      I’m aware of two motivations for such tricks:
      1) Force SQL to materialize an intermediate query. This can sometimes result in improved query plans, but it’s not particularly robust. Usually a temp table can accomplish the same thing more robustly.
      2) Allow order by in a view. This is generally an awful bad idea: the view is not guaranteed to stay in order.

      Reply
      • you can also do 2 billion.. I just tested it out.. it still works lol see here for screenshot: https://twitter.com/DenisGobo/status/1128715434948268032

        Code…

        create table F(x int primary key)
        insert f values (3),(5),(1)
        go
        create view f_vw –__CELKO__ approved name…. NOT
        as
        select top 100 percent *
        from F order by x desc
        go
        create view f2_vw –__CELKO__ approved name…. NOT
        as
        select top 2147483647 *
        from F order by x desc
        go

        select x f from f
        select x f_vw from f_vw
        select x f2_vw from f2_vw

        Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}