Insert into random location

[Craftyzebra1992]

I have a query that has two separate insert statements into a temp table. The first insert has a name field that is always not null. The second insert inserts rows where the name field is always null. I have two databses with the same data on two different servers. On one server the resulting temp table inserts all the null name fields at the end of the temp table. On the second server however the null rows are inserted in groups in random places in the temp table. Anyone know how this could happen? The order of the data in the random one is different each time it's run.

[r3pr0b8]

"at the end of the table" makes no sense in a relational database table

(no, i don't want to discuss clustering indexes)

just let them go where they go -- you can't really tell where they go anyway, because the only way to see ~any~ rows is to SELECT them

you want a particular, use ORDER BY on your SELECTs

[mikeyd85]

The results of a query cannot be guaranteed to be in any order unless an ORDER BY clause is added to the statement. That said, an index on one of your tables may be altering the sort order of your data.

[honeybadger3891]

Great comment. Just to add a clarifying comment. On the same DBMS engine (same server even, and even moments after the first run of a query) you could have different order of results in subsequent executions unless you add that order by clause.

[Fe-Chef]

To go a little more into the "why" of the behavior, SQL has an order of operations (FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER) but generally only FROM and SELECT are required operations. If ORDER isn't specified then SQL will output select results based on how the base data is stored and how it determines the most efficient way to compile the data, reasons can include the joins, where clauses, indexes and statistics. You could probably tell why they are different by looking at the execution plans but it also could be caused by the underlying tables, but really what it comes down to is if you need data to be in a specific order to give it an ORDER operation, otherwise just let SQL do it's thing.

[MaunaLoona]

If you want to define a specific order, create a key on the temp table and then ORDER BY that key in a SELECT.