-4

Let's say I have a table that has the following data inserted in order:

name     age
tom      20
sarah    30

Is there a way to retrieve the rows that orders it by its 'default ordering' (usually physical location on disk/memory)? Two examples might be:

SELECT * FROM tbl -- guarantee it's ordered properly either implicitly or explicitly
SELECT LAG OVER (ORDER BY ...?) FROM tbl

I know this is not a strictly ASNI SQL topic as the rows are not ordered when defined mathematically, but I'm looking to see if it's possible to implement this in actuality.

David542
  • 104,438
  • 178
  • 489
  • 842
  • 4
    Firstly, please don't tag multiple RDBMS because they are all different. I can tell you that SQL Server has no concept of default ordering. Unless you have a column you can order by you cannot be sure how they will be ordered. – Dale K May 18 '22 at 02:04
  • 4
    In SQL Server, a table is an unordered bag of rows (there is no "guarantee it's ordered properly"). If you expect a specific ordering, you need to use an `ORDER BY` clause for that to be predictable, because you cannot rely on physical ordering at all. If you don't have a column that allows you to correlate a row with its write sequence, you'll need to add one (IDENTITY, columns populated by a sequence, and datetime2 columns with default sysdatetime() are reasonably reliable ways to do this). [See #3 here](https://www.sentryone.com/blog/aaronbertrand/t-sql-tuesday-56-sql-server-assumptions). – Aaron Bertrand May 18 '22 at 02:11
  • 2
    [Also related](https://stackoverflow.com/a/19983752/61305) (again, for SQL Server - please only tag one DBMS because vendor implementations can vary). – Aaron Bertrand May 18 '22 at 02:14
  • @AaronBertrand interesting stuff thanks for those two links. I wonder then what conditions would need to be held for it to sort in insert order (maybe no deletions, no indexes that would result in a non-table scan from the query execution, etc.) – David542 May 18 '22 at 02:42
  • 1
    Just coming up with the list of all the things that would have to happen for it to _maybe_ work out would take longer than just adding a column that allows you to be sure. There is no point, it's simply not how an RDBMS is designed to work. It's not a text file. – Aaron Bertrand May 18 '22 at 02:44
  • Don't know about MySQL, but on SQL Server: the clustered index would have to be the *only* index; the plan would have to be serial not parallel; probably no joins or subqueries; not an allocation-scan, so not using `NOLOCK`. There are probably a bunch of other conditions I can't think of now. – Charlieface May 18 '22 at 02:54
  • @Charlieface the clustered index on _what_? The table in the question only has `name` and `age`. If there is a column added that _can_ guarantee order, then you can save yourself a lot of headache by simply adding `ORDER BY ` instead of trying to line up 8 billion stars to save typing a handful of characters. – Aaron Bertrand May 18 '22 at 02:59
  • @AaronBertrand OK so an implied condition that you must have a clustered index, otherwise you might get forwarded rows. I think we are agreeing vociferously about this..... – Charlieface May 18 '22 at 03:02
  • 1
    @Charlieface Yes, we agree. I was just clarifying that `the clustered index would have to be the only index` actually should be `the clustered index would have to be the only index _and have a leading key on a column that reflects insert sequence_`. And if such a column exists, there is no need to wonder about physical order or default order or any of that, because you could just explicitly order by that column. – Aaron Bertrand May 18 '22 at 03:05

2 Answers2

0

Since there can be no logical sort wthout using ORDER BY the physical order would be effectively invisible. The order of rows returned by a SELECT statement is undefined unless you use ORDER BY.

Nick M
  • 36
  • 1
  • 3
-3

Yes there is a way.

  1. create an extra datetime field default to getdate()
  2. create clustered index on the field created in step 1.
  3. select without any order by clause -- output is in the physical order which is same as the the order a row is inserted.
AIMIN PAN
  • 1,563
  • 1
  • 9
  • 13
  • Columns not fields – John Cappelletti May 18 '22 at 02:47
  • 2
    (1) do you not think it's possible for two rows to get the same datetime value on insert? (2) did the links supplied in the comments not demonstrate cases where this does not work (e.g. the presence of a covering, non-clustered index)? – Aaron Bertrand May 18 '22 at 02:47
  • 1
    [Here's an example](https://dbfiddle.uk/?rdbms=sqlserver_2017l&fiddle=eb2f3914221254f267d8ea1da8fa2285) showing that a clustered index won't necessarily be used to return rows in a specific order, and that a datetime column alone can't supply sufficient information to reliably sort either. – Aaron Bertrand May 18 '22 at 02:55