I've recently been chasing an issue with a client's db... solution found, but impossible to recreate.
Essentially, we're doing a
Select * from mytable where ArbitraryColumn = 75
Where MyTable has an Identity column, called 'MyIndentityColumn' - incremented by one in each insert. Naturally, and normally I would assume that the order returned would be the order in which they are inserted (bad assumption, but one which was forced onto me, through an inherited application - which has been patched).
Essentially, I would like suggestions as to why the database, when restored to my local machine (same OS, same SQL server version - 200 sp3) same collation, and same backup instance restored on it, as a test DB on the client site.
When I perform the above select, I get them in order of insert (i.e. identity column ordered ascending). On the client, it seems random (but the same 'random' order each time)...
A few other points:
- I have the same collation on my test server as client
- Same DB backup restored to a test only I can access
- Same SQL server version and service pack
- Same OS
- Test DB is a new DB - new log and MDF...
I have the problem 'solved' by adding an explicit order by clause but I want to undertand the cause of the issue, given the exact nature of my attempts to recreate it beuing futile, and perfectly recreatable on the client server...
Thanks in advance,