Why does the table randomly reorganize the rows?

Question

I have the following table

DBName  Server  Status  UpdateTime
DB1 server1 NULL    5/4/2019
DB1 server1 NULL    NULL
DB2 server2 NULL    6/4/2019
DB2 server2 NULL    6/4/2019
DB3 server2 NULL    NULL
DB3 server2 NULL    NULL

Sometimes, when I click select rows, it somehow rearranges some random rows. also if I click edit I notice the rows have been rearranged. Why does that happen?

DBName  Server  Status  UpdateTime
DB1 server1 NULL    5/4/2019
DB2 server2 NULL    6/4/2019
DB3 server2 NULL    NULL
DB3 server2 NULL    NULL
DB2 server2 NULL    6/4/2019
DB1 server1 NULL    NULL

I mean this doesn't really cause problems but I have 3 environments and it makes it difficult to compare the results across environments when the rows aren't in the same order.

At first it happened while I was working remotely from home. so I thought it could be VPN/network issue. But even at the office, it happened, where I'm not connected to VPN.

EDIT:

This is different from this question as I dont have a primary key

Does 'Select' always order by primary key?

SQL tables have no inherent ordering. This is by design. If you want to see things in a specific order consistently then use an `ORDER BY` clause in your `SELECT` query. — ADyson, Jun 04 '19 at 20:37
@ADyson so ive just been lucky to see it organized properly 99% of the time but it happens every while that select reorders stuff? — Cataster, Jun 04 '19 at 20:38
It doesn't so much consciously re-order things as just randomly display them differently. _Usually_ if you don't specify an explicit order then you tend to see things in the order they were originally inserted, but there's no guarantee of that. The DBMS is at liberty to return the rows in whatever sequence it finds it most convenient to read them from the table. I don't know enough about the internals of SQL Server to know how it might make such a decision, but I do know that it's perfectly free to do so according to the SQL specification. — ADyson, Jun 04 '19 at 20:39
@ADyson damn :/ this is very annoying lol. well thank i know now at least i am not doing something wrong on my end — Cataster, Jun 04 '19 at 20:41
@ADyson true, can i specify an order sequence? like for example ORDEBY DBName (DB2,DB3,DB1)? — Cataster, Jun 04 '19 at 20:44
No. But you can specify ascending or descending order. And you can order by more than one field (e.g. you could order by DBName and then by UpdateTime, something like that. And the ascending/descending order of each of those fields could be different.) See the [documentation](https://learn.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-2017) for full details. — ADyson, Jun 04 '19 at 20:46
@ADyson eh, i was looking to order by classification field i have in my table. im not particularly looking for ascending/descending but more by what i have specified., and not necessarily alphabetically — Cataster, Jun 04 '19 at 20:47
well you can't. You can't order the data in a totally arbitrary sequence like that - it's not a natural sort of that data type. Strings are sorted alphabetically. — ADyson, Jun 04 '19 at 20:49
A way round that would be: Assuming you have a parent "DBNames" table or something like that which has unique entries for each DB Name (and that this table you've shown us has a foreign key to that table), then you could add an extra "SortBy" column to that table, just using numbers. So then you could join that key table to your main table, and use the SortBy column to create your arbitrary ordering of DB1, DB3, DB2 (in this case you'd obviously assign SortBy values of 1, 2, 3 to each of those respectively in the key table) — ADyson, Jun 04 '19 at 20:51
Why do you expect the same order? You were never told that you could. PS (Obviously--) This is a faq. Before considering posting please always google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names; read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. — philipxy, Jun 05 '19 at 02:03
Please [use text, not images/links, for text--including tables & ERDs.](https://meta.stackoverflow.com/q/285551/3404097) Paraphrase or quote from other text. Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Include a legend/key & explanation with an image. — philipxy, Jun 05 '19 at 02:04
Possible duplicate of [Does 'Select' always order by primary key?](https://stackoverflow.com/questions/824855/does-select-always-order-by-primary-key) — philipxy, Jun 05 '19 at 02:21
@philipxy hmm at this point I cant delete this post since it has an answer :/ I'll keep your comments in mind for future questions I may have — Cataster, Jun 05 '19 at 02:30
"See [ask] & the voting arrow mouseover texts." "This question does not show any research effort; it is [...] not useful" The duplicate answer answers your question & lots of closer duplicates of your question are also out there, I just picked the earliest easily found that explained that tables have no order. PS If you edited in a preface that this is a duplicate & a statement of the answer & maybe a a duplicate link you like then you might get undownvoted and/or no more downvotes. There is a way for the owner to close as duplicate. — philipxy, Jun 05 '19 at 03:38
Possible duplicate of [What is MySQL row order for “SELECT * FROM table_name;”?](https://stackoverflow.com/questions/1949641/what-is-mysql-row-order-for-select-from-table-name) — philipxy, Jun 05 '19 at 03:38

ADyson · Accepted Answer · 2019-06-04T21:43:48.670

SQL tables have no inherent ordering. This is by design. If you don't specify the order then the DBMS is free to return the rows in whatever order it sees fit. This often turns out to be the order in which the rows were originally inserted, and so people sometimes assume that is is the natural or inherent order of the table. But in fact there is nothing which guarantees this, so you cannot rely on it. Occasionally, as you've noticed, the DBMS may decide to do things differently, for its own reasons.

If you want to see things in a specific order consistently then fortunately the solution is clear and easy: you must use an ORDER BY clause in your SELECT query.

If you need to order things in a non-natural way (e.g. ordering a varchar column non-alphabetically) then a standard workaround is to add an extra "sort" column as a proxy, and order by that instead.

Why does the table randomly reorganize the rows?

1 Answers1