Return rows in the order in which they were inserted

Question

I have a simple join table with two id columns in SQL Server. Is there a way to select all rows in the order in which they were inserted?

If I try to make SELECT *, even if I do not specify an ORDER BY clause, the rows do not return in the order in which they were inserted, but are ordered by the first column.

I know this is a strange question, but this table is very large, and I need to check exactly when the strange behavior started, and, unfortunately, I do not have a timestamp column in my table.

UPDATE # 1

I will try to explain why I say that rows do not return in natural order when I SELECT * FROM table without an ORDER BY clause.

My table was something like this:

id1 id2 --------------- 1 1 2 2 3 3 4 4 5 5 5 6 ... and so on, with about 90.000+ rows 

Now I don’t know why (probably a software error inserted these rows), but my table has 4.5 million rows and looks something like this:

 id1 id2 --------------- 1 1 1 35986 1 44775 1 60816 1 62998 1 67514 1 67517 1 67701 1 67837 ... 1 75657 (100+ "strange" rows) 2 2 2 35986 2 44775 2 60816 2 62998 2 67514 2 67517 2 67701 2 67837 ... 2 75657 (100+ "strange" rows) 

Crazy, my table now has millions of rows. I have to see when this happened (when the rows that were inserted), because I have to delete them, but I can’t just delete using * WHERE id2 IN (strange_ids) *, because there are "correct" id1 columns that these id2 columns belong, and I can't delete them, so I'm trying to see exactly when these rows were inserted to delete them.

When I SELECT * FROM table, it returns me by id1, as in the above table, and the rows were not inserted in this order in my table. I think my table is not corrupted because the second time this strange behavior happens the same way, but now I have so many rows that I can delete manually as it was the first time. Why don't rows return in the order they were inserted? These "weird rows" were finally inserted yesterday and should be returned closer to the end of my table if I do SELECT * without ORDER BY, right?

Answer 1

As others have already written, you cannot retrieve rows from a link table in the order in which they were inserted.

If there is some kind of internal row order in one or both tables that this link table joins, you can use this to try and find out when the link table rows were created. In principle, they cannot be created BEFORE . Both lines containing PK: s were created.

But, on the other hand, you cannot find out how long they were created.

If you have decent backups, you can try to restore one or more backups of different ages, and then try to find out if these backups contain this strange behavior. This may give you at least some idea of ​​when the weirdness started.

But the bottom line is that, using only select, you can now get a row from a table like this in the order in which they were inserted.

Answer 2

A select query without order by does not retrieve rows in any particular order. You must have an order by in order to receive an order.

SQL Server does not have a default method for inserting in insert order. You can do this if you have information in the line. The best way is a primary key identification column:

 TableId int identity(1, 1) not null primary key 

This column increases as each row is added.

You can also have a CreatedAt column:

 CreatedAt datetime default getdate() 

However, this may have duplicates for simultaneous insertions.

The key point is that the select clause without the order by clause returns an unordered set of rows.

Answer 3

If SELECT * does not return them in a natural way, and you did not insert them with a timestamp or automatically increasing identifier, then I believe that you are sunk. If you have an IDENTITY field, order it. But the question I have is, how can you say that SELECT * does not return them in the order in which they were inserted?

Update: Based on your update, it seems that there is no way to return records as you wish, I would suggest that you have a clustered index on ID1?

Answer 4

 Select *, %%physloc%% as pl from table order by pl desc