MSSQL - Insert Into adding data into the middle of the table?
There is no &quot;middle of the table&quot; - 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.