SQL Server - Inserting Records into Table Produces Different Ordering

[clamum]

I am trying to figure out why inserting records into a table I'm working with is putting them in different order each time I insert them.

What's happening is a script runs that truncates this table (among others). Then another script does an insert into this and other tables, from a Staging database. If I run these scripts, say, twice, then a SELECT * of the table in question is showing the records in different order after each run. A SELECT * of the Staging table shows no change at all to it. I'm using Beyond Compare for comparison purposes, by the way.

The weird thing is, is that this different ordering is not happening on another table. Same procedure: truncate table, then insert into it from Staging. The only difference I saw was that the "INSERT INTO TableGood (columns) SELECT columns FROM Staging.TableGood" actually explicitly listed each column in the INSERT and SELECT clauses. The table that was showing different record ordering just had "INSERT INTO TableBad SELECT * FROM Staging.TableBad". Naturally I added columns to the TableBad INSERT/SELECT but that did not resolve the issue. I also changed the TRUNCATE to a DELETE FROM and that also did not resolve the issue.

Any ideas? I'm kinda stumped here, and need to figure this out as it's causing an issue in something else that's a high priority. Thanks much guys.

 

[Ken g6]

Seems like, if you want a guaranteed order, you should provide an index column that enforces that order, and ORDER BY that.

 

[nakedfrog]

Agreed, sounds to me like maybe there's no primary key on the source table.

 

[matricks]

Seems like, if you want a guaranteed order, you should provide an index column that enforces that order, and ORDER BY that.

This. SQL isn't required to adhere to any ordering, unless specified in the query. Relying on a particular order without ORDER BY is flawed application design. If you want it ordered, you ORDER BY in your query. That's the only guaranteed ordering you should rely on.

 

[clamum]

You guys got it. Just after I posted this, I decided to check the table structures. Sure enough, the table that was giving me different ordering did not have a primary key. I guess I knew that all along but I didn't think of it until after posting.

After adding a primary key (composite key from two columns since there isn't a single one that uniquely identifies rows) and re-running the scripts, the ordering is identical.

I'll report my finding to my supervisor/co-worker, but I have a strong feeling the response will be "adding a primary key will impact a lot of things [basically true]. Find another solution" (even though the table should've had a fucking key in the first place). :-/

EDIT: I guess the other solution would just be to add an "ORDER BY" clause to the end of the "INSERT INTO x SELECT FROM x" query.

 

[Mark R]

EDIT: I guess the other solution would just be to add an "ORDER BY" clause to the end of the "INSERT INTO x SELECT FROM x" query.

This is the only "real" solution.

SQL can return records in any order, unless you have an ORDER BY clause.

Usually, they will be returned in order of the primary key - but some database engines may use various optimizations which can change the order (e.g. by changing the execution plan for the query) and make it unpredictable.

 

[clamum]

This is the only "real" solution.

SQL can return records in any order, unless you have an ORDER BY clause.

Usually, they will be returned in order of the primary key - but some database engines may use various optimizations which can change the order (e.g. by changing the execution plan for the query) and make it unpredictable.

Yeah this is what we'll probably do. But I'm pretty sure I'll have to figure out how the current INSERT is ordering the records, so I can do the same thing so the results match and I can show that on my testing paperwork so it can be signed off on. Yikes... I don't have the data in front of me right now, but it didn't seem like there was a particular order to it.

 

[Ken g6]

This is what auto-indexing is for. You add a column with auto-indexing, and it adds a new number, starting with 1, for every new row added, in order. Apparently, SQL Server uses the IDENTITY keyword.

But it sounds like you may not have access to the table that the data was originally added to.

 

[h4rm0ny]

Yeah this is what we'll probably do. But I'm pretty sure I'll have to figure out how the current INSERT is ordering the records, so I can do the same thing so the results match and I can show that on my testing paperwork so it can be signed off on. Yikes... I don't have the data in front of me right now, but it didn't seem like there was a particular order to it.

As another poster has already explained, you have a fundamental misunderstanding. The records DO NOT HAVE an order. The INSERT is not "ordering your records". That statement is absolutely not correct. Your response is a little like someone saying "okay, I get that a cockatoo is actually a bird so I'll probably change things, but I still need to work out what sort of dog it is before I can sign off on this."

Records do not have an order. There are all sorts of ways a database engine might store those records and if you pull them out without an ORDER BY clause then they will typically come back in the same sequence just because the software is pulling them out in the same way, but that is not the same as them having an order. You could get a different sequence back even without doing that INSERT for all you know. There are several reasons why that could happen.

Records do not have an order, ever. Thinking they do is a fundamental misunderstanding of SQL. Trying to work out what order your INSERT has left them in is a question without a right answer and if your testing relies on that, you need to go back and revisit your tests because they cannot be passed.

 

[Mark R]

Yeah this is what we'll probably do. But I'm pretty sure I'll have to figure out how the current INSERT is ordering the records

INSERT does not order the records.

In SQL, there is no order without ORDER BY.

 

[nakedfrog]

Just to clarify what the above posters are saying... the INSERT is basically just putting the records in the order that it received them. It's the SELECT without an ORDER BY that is producing the inconsistent ordering of your data.
The results from the existing SELECT are going to depend on a lot of various conditions, and I don't think there's going to be a feasible way to reproduce it after the fact--certainly if it's plausible it's going to take more time than fixing the actual problem.

 

[DaveSimmons]

Yep, to beat the horse one more time:

Without ORDER BY, SELECT is free to return records in any order that it wants.

It might be the order in the disc file but there is no reason to expect that to match up with the INSERT, especially if any rows have ever been deleted.

File on disc after rows 1-10 inserted
[ 0 1 2 3 4 5 6 7 8 9 ]

Delete rows 2,6,8 insert rows A,B,C,D
[ 0 1 A 3 4 5 B 7 C 9 D ]

So SELECT * returns A,B,C before 9.

That's just one possible ordering though. The database was free to store the rows in any order that it wanted so maybe it stored 0 - 9 as [ 9 8 7 6 5 4 3 2 1 0 ] and inserting A,B,C,D gives you 9 8 7 6 5 4 3 2 1 0 D C B A.

Any ordering you think you are getting without using ORDER BY might depend on the specific database engine (and version of it) and also on whether there have been any deletes.

 

[clamum]

INSERT does not order the records.

In SQL, there is no order without ORDER BY.

I know that. I didn't mean INSERT was ordering the records; I was simply referring to the order in which they were placed into the table. I was referring to the strict dictionary definition of "order," not as it pertains to SQL. Anyway, it doesn't matter, as I was thinking too much into the problem and anticipating what I was going to be asked to do; it turned out I just added the "ORDER BY" and all is good.