Perfectly reproducable select statement default ordering issue....
- by Dave
Hi,
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,
Dave
