MSSQL - Insert Into adding data into the middle of the table? [duplicate]

Question

A bit of a random one here... I've got a basic table that I'm using for temporary data inserts - and for that reason, I don't need or want a unique ID - however, I'm seeing odd behaviour! (or it feels odd to me at least)

If I insert data into the table, and then immediately run a select statement on the same table (no ordering going on) - the new row ends up in the middle of the results - how / why ?

This isn't a problem for me - I don't particularly care how it orders the data for this particular table (and I'm aware we could add a unique column, PK etc, autoinc identifier etc to handle this situation) - but it's just something I've never seen before - I'm convinced that on the hundreds of occasions I've done this previously, new data always ends up at the bottom of the list! - Am I going mad? is this normal? is there a way to always insert data to the end ?

Thanks

Chris

Answer 1

There is no "middle of the table" - a table is an unordered set of rows. Think of it as a bag of marbles - if you empty it on the floor, can you predict which marble will stop moving first, last, etc.?

If you don't use ORDER BY in your query, SQL Server is free to return the data in any order it chooses, and it will do based on what is most efficient. This won't necessarily align with order of insert, especially if you have a clustered index that is unrelated.

If you want a select to return a predictable order, use an ORDER BY (which may require adding a column like an identity column or an auto-generated datetime column.)

If you want predictable order without using ORDER BY, you are pretty much out of luck. You could use a naturally increasing key for your clustered index, which will make it more likely to observe that order, but SQL Server still doesn't have to return data in that sequence, and it could change at any time due to changes in indexes, stats, etc. See #3 here.

What you remember from all of your previous experiences probably involved a clustered index on an identity column, which is highly likely to exhibit that behavior without any other influencing factors.