0

I have additional question related to topic "The order of a SQL Select statement without Order By clause"

Let's assume we need to demonstrate that we must use Order By to return rows in specific order. So we want to show that SELECT returns rows in THE RANDOM order, or that INSERT inserts rows in random order, based on T-SQL

DECLARE @NotSortedTable TABLE(
             ID INT NOT NULL,
             C1 INT, 
             C2 FLOAT,
             C3 DATETIME,
             C4 VARCHAR(5)           
             )

INSERT INTO @NotSortedTable 
        VALUES (3, 3, 3.0, '26 oct 2018', 'Z'),
         (2, 2, 2.0, '25 oct 2018', 'Y'),
         (1, 1, 1.0, '24 oct 2018', 'X')

SELECT * FROM @NotSortedTable

What should I change in this SQL to make rows returned in WRONG order, not in the order how it was inserted into database.

Additional question:

When I do SELECT, it seems data ALWAYS returned in very specific order

ID  C1  C2  C3  C4
3   3   3   2018-10-26 00:00:00.000 Z
2   2   2   2018-10-25 00:00:00.000 Y
1   1   1   2018-10-24 00:00:00.000 X

Where exactly this order defined in database? Is this some property, index or second on HDD? or something else?

Help appreciated

justromagod
  • 933
  • 9
  • 20
  • [What is the default Order By of queries](https://stackoverflow.com/a/8746712/2794280) – kgzdev Oct 26 '18 at 07:37
  • 3
    It doesn't return rows in a *random* order. It returns them in an *arbitrary* order. It's important to realise that those are different things. You can run 1000s of trials and always observe the "same" order of returned rows. That doesn't somehow make that order *guaranteed*. – Damien_The_Unbeliever Oct 26 '18 at 07:37
  • You should a slightly modify your test to show that row order is not guaranteed. Create a physical table instead of table variable, store the table on three partitions distributed by ID (partition 1, 2 and 3). After inserting the data the SELECT order will be inverse that actual. – serge Oct 26 '18 at 09:44
  • Your example has too little data, it will all fit on one page and that will ensure your test always returns the same order. You need more rows and bigger datatypes to demonstrate this. – Sean Pearce Oct 26 '18 at 10:25
  • I don't think I created duplicated question, I extended original question with request to create example to demonstrate issue. and placed it into separate topic, in order to get example. (If some SQL expert decide spent some time one this) – justromagod Oct 26 '18 at 12:45

0 Answers0