1

enter image description here

I have table with columns, some columns are varchar. I have noticed that the rows in the table are sorted automatically. I, instead, want the rows to be in the same order as they are inserted into the table. Any clues? Please note that I haven't applied any ORDER BY clause and Dates are all same for the columns.

As is evident that although I added Testing Book 3 first, it automatically came below the Testing Book 2 which is not desired.

Is it because my PK is composite?

Samarth Agarwal
  • 2,044
  • 8
  • 39
  • 79
  • 2
    Add an automatically incremented ID column as a PK. – SimpleVar May 04 '13 at 06:26
  • Can't do that, My table structure does not allow me to do that. I have already specified PK on a group of columns. – Samarth Agarwal May 04 '13 at 06:32
  • What columns are in the composite PK that you have specified? – J0e3gan May 04 '13 at 06:34
  • The rows in the table are **not** sorted automatically - in fact, in a SQL Server table, there is **no** inherent sort order at all. The data in SQL Server is not sorted - only your output can be sorted - **if and only if** you provide an `ORDER BY` clause. – marc_s May 04 '13 at 08:07
  • Composite Key invoice_ID (numeric), invoice_account (varchar), invoice_item (varchar) – Samarth Agarwal May 04 '13 at 10:00
  • Well, I have posted the screenshot as well showing sorting, and I am sure that I havn't specified any order by clause. – Samarth Agarwal May 04 '13 at 11:31
  • @SamarthAgarwal, you may be seeing sorting, but it is not **guaranteed** sorting; it is just what SQL Server did at that moment, even if it happens the same way 1000 times in a row. As I, and now marc_s, have said, the **ONLY** way to guarantee a particular sort is via ORDER BY. – Solomon Rutzky May 04 '13 at 14:08
  • In the picture above, I have clearly inserted the 2nd row before the 1st row then how come it is shown 2nd. I just need to understand where I am lacking, no offence. – Samarth Agarwal May 04 '13 at 16:18
  • @SamarthAgarwal: you are confusing what "appears" to be happening with what is actually happening. The rows are not _necessarily_ stored in the order they were inserted. And depending on how you organized your CLUSTERED index, doing a REBUILD can change the physical location of the data pages. And that is all irrelevant if the query goes parallel as the different threads can be reassembled in any particular order. The **ONLY** way that SQL Server knows what the end-result sorting should be is via the ORDER BY clause. There is no other way to get consistent / guaranteed sorting. – Solomon Rutzky May 04 '13 at 17:04

2 Answers2

5

You did not specify exactly which RDBMS you are using, but I can say the following with regards to Microsoft SQL Server:

  1. You CANNOT guarantee ANY predictable / repeatable ordering without an ORDER BY clause

  2. If you want rows to be ordered by when they were inserted, you need to: add a new column that is either an IDENTITY (could be INT or BIGINT) or a DATETIME / DATETIME2 datatype with a default constraint of GETDATE() or GETUTCDATE() AND ORDER BY this new field

  3. The new field has nothing to do with a PK. This is in reference to a suggestion someone else made. A PK is for relationships, not sorting, and while an IDENTITY is typically used for a PK, there are plenty of situations to have a PK of one or more non-auto-incrementing fields and still have an auto-incrementing field.

  4. If you need the detail on what millisecond / nanosecond the records are inserted as well as the guaranteed / repeatable sort, then do both the DATETIME / DATETIME2 and IDENTITY fields.

  5. Adding one, or both, of these fields does not imply any specific index structure. Their existence merely allows you to create one or more indexes that would include them to enforce your desired ordering.

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
  • There is one more concern. I am inserting some rows at once, so, they will have the same datetime, then how do I order them? – Samarth Agarwal May 04 '13 at 11:28
  • 1
    If they were truly inserted at the same time, then the ordering of those rows doesn't matter according to your criteria. – Ben Thul May 04 '13 at 13:12
  • @SamarthAgarwal, I agree with Ben in that if they were inserted at the same time and you want them to be in that order, then there is truly no difference between two (or more) rows inserted at the same millisecond (or nanosecond if using DATETIME2). _However_, if you want a predictable / repeatable sort, then that is why I also suggested adding an INT / BIGINT field that is marked as IDENTITY instead of doing the DATETIME / DATETIME2 field. If you need the detail on what millisecond the records are inserted plus the repeatable sort, then do _both_ the DATETIME and IDENTITY fields. – Solomon Rutzky May 04 '13 at 14:15
  • Well i assumed the same **but** they are getting sorted right after getting inserted. Note: It all works fine if I remove the Composite PK. Any Clues? – Samarth Agarwal May 04 '13 at 16:13
  • @SamarthAgarwal: NO, it is NOT working fine when you remove the PK. You are just seeing the order that the rows are in based on several factors that could all change. That is why I keep stressing _guaranteed_ and _repeatable_. What you are seeing is NOT a sort but a coincidental ordering that matches your expectations. Once the data is large enough to maybe cause the optimizer to choose a parallel plan, you will see rather "odd" sorting. That is why you **need** an ORDER BY clause. End. Of. Story. Put the PK back, add the 1 or 2 fields, and use it/them in an ORDER BY. There is no other way. – Solomon Rutzky May 04 '13 at 16:55
  • Ok ok don't be angry. :) Thanks – Samarth Agarwal May 05 '13 at 12:45
  • @SamarthAgarwal: you are welcome, and sorry to show frustration. – Solomon Rutzky May 05 '13 at 13:47
1

Please note that SQL does not guarantee ordering when inserting or selecting rows. You can see answers for a question similar to yours here

The way I would do ordering by insertion is I would add DATETIME column that gets a date/time value of when you do insert.
(How this can be done you can see in the accepted answer to this question)

Then during selects make an order by on the DATETIME column

Community
  • 1
  • 1
Michael
  • 860
  • 7
  • 19