-1

This might sound silly and it doesn't affect my code but out of curiosity, what causes the auto increment "ID" to be reversed like that and not increments correctly?

enter image description here

The structure: enter image description here

2766
  • 145
  • 11
  • 3
    There is no reason to assume that the rows are being displayed in the order they were inserted - I assume thats what you are referring to? In SQL Server rows are only ordered in a known manner when there is an `order by` clause. – Dale K Dec 02 '20 at 08:45
  • did u change the clustred index ? – Ajt Dec 02 '20 at 08:46
  • @Ajt no i didn't – 2766 Dec 02 '20 at 08:51
  • @DaleK but all the other table and this specific table used to be ordered incrementally by this ID. why this sudden change in behaviour? – 2766 Dec 02 '20 at 08:52
  • 1
    Do you have a screenshot of the table structure? That would show us what is going on. – etch_45 Dec 02 '20 at 08:55
  • 1
    Who knows, why bother? If you want the result in the ID order, use ORDER BY. Never, ever build an application depending on an "assumed" order. – jarlh Dec 02 '20 at 08:58
  • 1
    @2766 there is no way of knowing why and its not relevant as its the way SQL Server works... unless you specify order by, you get random... which is some cases (enough to maybe make you think its always the case) is the insertion order. – Dale K Dec 02 '20 at 09:15
  • @jarlh I just think it's a little weird to just suddenly be in non-order like that – 2766 Dec 02 '20 at 10:11
  • @2766 it *could* be because SQL Server split a page and moved stuff around on the disk... thats the thing, you are saying the inner workings of SQL Server are weird without actually knowing what its doing under the hood. Nor should you know what goes on under the hood... PS your added screenshots don't change anything, other than to make your question very long. – Dale K Dec 02 '20 at 10:26
  • @DaleK I added them because someone asked me to and it was just a general question that's all it didn't affect my code or anything . – 2766 Dec 02 '20 at 10:33
  • duplicate of: https://stackoverflow.com/questions/10064532/the-order-of-a-sql-select-statement-without-order-by-clause – Mitch Wheat Dec 03 '20 at 04:47

2 Answers2

-1

The auto-incremented id is fine. The issue is the query, which is presumably something like:

select *
from users;

This query has no order by. Hence, the results can be returned in any order. And the order can change from one row to the next.

To see the results in a particular order, you always need to include order by in the query. This query may be generated by a tool, so you have no control over that.

In practice, I would expect the results on a small table to be in the order of the clustered index. Let me emphasize that this is not guaranteed, but is often the case. So, if a different column is declared as the primary key or explicitly as the clustered index, then the ordering might appear to be by that column.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
-2

I think ID is not the primary key. If you can make ID as the primary key the row should come in the correct order.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Zenesys
  • 1
  • 1
  • 1
    This answer is toitally incorrect. Unless there is an ORDER BY, you should not assume any particular order. On top of that, you are confusing primary key and clustering key. – Mitch Wheat Dec 02 '20 at 10:02