1

Let's say I have a table with a three column primary key. If I select all from that table without any order by clause they are, to my understanding, ordered by these columns. The first one, and within that by the second column and within that by the third.

Is there any additional cost, or perhaps performance gain, by explicitly adding a order by clause with the three columns in the order they are part of the primary key?

inquam
  • 12,664
  • 15
  • 61
  • 101

1 Answers1

2

If I select all from that table without any order by clause they are, occurring to my understanding, ordered by these columns

Your understanding is incorrect. Without an order by the database engine may output the results in any order it chooses. In fact if you were to add a clustered index on another field it is likely to change the order of the result.

Is there any additional cost, or perhaps performance gain

There will never be a performance gain by ordering (unless you're doing something that effects branch prediction ). There will be no cost if the database was going to order that way. However there may be a correctness issue if you're not specifying the order.

Community
  • 1
  • 1
Conrad Frix
  • 51,984
  • 12
  • 96
  • 155
  • Ahhh the small memory I had about some implicit rules seem to have been when a group by was used and no explicit order by. – inquam Jul 06 '16 at 19:32