0

I have an SQL Server table that i post into using C#. I would like the fields to be ordered using Id. Instead i get something like this:

enter image description here

I need the id column to be arranged in order of increasing number because i use a data table in C# to go through these values one at a time, therefore jumping from 7 to 9 to 11 back to 8 is very inconvenient.

N.B: I'm not talking about select * from table_name order by id. I need the values to be 'permanently stored' in the order requested.

0
8

Now, when you say:

I need the id column to be arranged in order of increasing number because I use a data table in C# to go through these values one at a time, therefore jumping from 7 to 9 to 11 back to 8 is very inconvenient.

it shows some confusion. There are two issues here that are orthogonal to each other:

  • the internal order that rows are stored

  • the order that the rows are returned in a result set

These two are not to be confused. The internal order is a matter of the database's physical design while the order of a result set can vary from query to query, depending on the ORDER BY clause.

Tables are inherently unordered. Despite that, in many DBMS - and in this case SQL Server - we have the option to add a "clustering index" on a table, so there is a B-tree index (with the chosen order) that leads to the rows.

This is however irrelevant to the order that a result set is presented. The only way to have a guaranteed order in the result set is to specify an ORDER BY clause.

If you don't specify an ORDER BY clause, the order can be arbiratry:

  • you may have a heap (table without clustered index) and still sometimes see the result ordered by id and sometimes in a random order.

  • you may have a clustered table and still sometimes see the result ordered by id and sometimes in a random order.

Notice that there is no difference above. The underlying table structure doesn't guarantee an order.


So, if you do want the rows stored in a specific order on disk, make (id) the clustered index. But note that without an ORDER BY id, you will still get the results in arbitrary order.

1
  • You're right. I have specified the Select * from table_name ORDER By Id statement on my C# end so that datatable is populated in this order. I have resorted to using this method after considering that the user has no care of how data in the database is arranged. By doing so i guarantee my preferred order in my 'datatable' instead of the database: I will leave the database arranged in its current state. – T. Mas Oct 10 '16 at 14:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.