How to make SQL Server order fields by 'Id' (which in this case is an Identity column)
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.