0

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

0

1 Answer 1

3

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.

1
  • Should mention that if the table is clustered by the ORDER BY column(s), the sort operation will be relatively inexpensive compared to sorting the same data with an auto-incrementing row pointer.
    – user212533
    Commented Jan 21, 2021 at 15:08

Not the answer you're looking for? Browse other questions tagged or ask your own question.