4

Running the following query returns 4 rows. As I can see in SSMS the order of returned rows is the same as I specified in the IN clause.

SELECT * FROM Table WHERE ID IN (4,3,2,1)

Can I say that the order of returned rows are ALWAYS the same as they appear in the IN clause?

If yes then is it true, that the following two queries return the rows in the same order? (as I've tested the orders are the same, but I don't know if I can trust this behavior)

SELECT TOP 10 * FROM Table ORDER BY LastModification DESC

SELECT * FROM Table WHERE ID IN (SELECT TOP 10 ID FROM Table ORDER BY LastModification DESC)

I ask this question because I have a quite complex select query. Using this trick over it brings me ca. 30% performance gain, in my case.

Zsolt
  • 365
  • 3
  • 12
  • 3
    No, the order is not garanteed like in the `in`clause. – juergen d Oct 31 '13 at 10:35
  • I think order of return result is based on primary key column if no order by clause specified – Upendra Chaudhari Oct 31 '13 at 10:39
  • I know that when 'order by' not specified, then the order of the result is not deterministic, but can you imagine that SQL server processes the values in random order in an IN clause? In my case this ID is the primary key – Zsolt Oct 31 '13 at 10:44
  • SQL server processes records as it wants; this depends even on physical file layout. You should *never* assume any implicit order - neither by primary key nor by clustered index. Your second set of queries can return records in different order; you can add additional "order by" to outer query however. – Arvo Oct 31 '13 at 11:08
  • @Zsolt See http://stackoverflow.com/questions/32356519/selecting-id-in-a-certain-order-specified-within-in-operator/32356625#32356625 – Lukasz Szozda Sep 02 '15 at 15:08

1 Answers1

3

You cannot guarantee the records to be in any particular order unless you use ORDER BY clause. You may use some tricks that may work some of the time but they won't give you guarantee of the order.

Szymon
  • 42,577
  • 16
  • 96
  • 114
  • 1
    It is true for select queries, I know. But I am not sure if it is true for IN clause also, because when you use IN clause, you explicitly define the order of values, like 'IN (4,3,2,1)'. – Zsolt Oct 31 '13 at 11:46
  • 2
    The results are likely to be returned in the same order whether you do `IN (1,2,3,4)` or `IN (4,3,2,1)` or any other order inside `IN`. – Szymon Oct 31 '13 at 11:53
  • 2
    @user1640463: As @Szymon said, the only guranteed way to get records in a particular order is to use `ORDER BY`. Your sample using `... IN (4,3,2,1)` is **not** guaranteed to return rows in that order. If you rely on that, you will sooner or later get in trouble when it no longer works... – user1429080 Oct 31 '13 at 11:58
  • 2
    Unfortunately you are right! It seems that the engine returns the rows in the order of PK (at least in the tests I've just tried, which is not guaranteed). Thank you! – Zsolt Oct 31 '13 at 12:00